sql - find which folder is not used

Question

I'm trying to find folders that are not in used when files are uploaded. I have 2 tables -

folder table - where folder names are store (folderID, folderName, userID)
files table - where the files are uploaded to (fileID, fileName, folderName, userID)

When a user uploads a file, they select a folder name from the folders table and then proceed.

I'm trying to find the folder names that are not used by the user, which are stored in the folders table but i can't figure out the correct sql statement to find those unused folders.

Which database are you using? MySQL or SQL Server? – Gordon Linoff Jan 12 '14 at 22:07 — Gordon Linoff, Jan 12 '14 at 22:07

score 0 · Answer 1 · answered Jan 12 '14 at 22:15

You could do a join like so

select f.folderID, f.folderName, fi.userID from folders f
left join files fi on fi.userID = f.userID

All of the records that return null for the userID column would indicate that those folders are not associated with any users.

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

You can use NOT EXISTS, its advantages are describe in this answer from SO.

SELECT * FROM folders WHERE NOT EXISTS (SELECT id FROM files WHERE files.folderName = folders.name);

See it work in this SQLFiddle.

edited May 23 '17 at 12:29

Community

1
1

answered Jan 12 '14 at 22:16

kero

10,647
5
41
51

score 0 · Answer 3 · answered Jan 12 '14 at 22:18

0

How about

select * from `folder` 
left join `files` on `files`.`folderName` = `folder`.`folderName`
where `files`.`fileID` IS NULL

You can check here

http://www.sqlfiddle.com/#!2/e225ec/1

answered Jan 12 '14 at 22:18

Abhik Chakraborty

44,654
6
52
63

gfrobenius · Accepted Answer · 2014-01-12T22:52:02.293

0

This example uses your same table setup: http://sqlfiddle.com/#!2/6935a/1

Based on your table setup, since userID is in both tables I'm assuming you want to include the userID join in determining which folders are NOT used, which some of the other answers do not do.

I personally wouldn't setup my tables this way though. I would link the tables using the folders primary key instead of it's name.

select * from folders f
where folderName not in
(
    select folderName from files i
    where i.userID = f.userID
)

UPDATE -- To show folders not used for the current user signed in...

select * from folders f
where folderName not in
(
    select folderName from files i
    where i.userID = f.userID
)
and userID = ?

Then of course you have to supply your bind vars from php (the user's ID). Hope this helps.

edited Jan 12 '14 at 22:52

answered Jan 12 '14 at 22:27

gfrobenius

3,987
8
34
66

thanks for that. how can i adapt this statement to allow for a variable to search for the folders of current user logged in? – user1386999 Jan 12 '14 at 22:34
Are you saying you want to search the folders returned from THIS query? If so, see updated answer. – gfrobenius Jan 12 '14 at 22:42
yes. the main purpose of this sql statement is to find the unused folders that are stored in the folders table, which the user will then have the option to delete. i want to find the folders that are unused/not used by searching for them using the userID of the logged in user. – user1386999 Jan 12 '14 at 22:47
Sorry for all the edits, see updated answer. – gfrobenius Jan 12 '14 at 22:49
@user1386999 did this or other answers help any? Up-vote/Answer? Or do you need more help? – gfrobenius Jan 13 '14 at 00:19

sql - find which folder is not used

4 Answers4