CODEWITHSUNDEEP
sql
Muepe
Muepe

Reputation: 671

Select element where condition is met on all joined elements

I found it hard labeling this question with an appropriate title but maybe thats because i dont know how to accomplish my goal.

I have the following database layout:

table:   folders
columns: folderId

table: files
columns: fileId, authorId

table: file_relation
columns: folderId, fileId

table: authors
columns: authorId, surname, firstName

What i try to achieve is listing all folder that only contain files from author X Y (surname = X, firstName = Y). My approach was like this:

SELECT f.folderId FROM `folders` f 
     INNER JOIN `file_relation` rel ON rel.folderId = f.folderId 
     INNER JOIN `files` fls ON fls.fileId = rel.fileId 
     INNER JOIN `authors` a ON a.authorId = fls.authorId 
WHERE a.surname = X and a.firstName = Y;

Now that lists me all the folders where there are files from said author. But how do i now restrict them to only get listed if there is no other file?

Upvotes: 0

Views: 51

Answers (1)

max
max

Reputation: 2817

It's better to use aggregate function instead of subquery. The query will look like this:

SELECT f.folderId,
    COUNT(fls.fileId) as filesCount,
    SUM(a.surname = 'X' and a.firstName = 'Y') as authorFilesCount
FROM `folders` f 
     INNER JOIN `file_relation` rel ON rel.folderId = f.folderId 
     INNER JOIN `files` fls ON fls.fileId = rel.fileId 
     INNER JOIN `authors` a ON a.authorId = fls.authorId 
GROUP BY f.folderId
HAVING filesCount = authorFilesCount;

Upvotes: 1

Related Questions