Gloomy Gloomy - 15 days ago 5
SQL Question

SQL : How to state that no element of a set can be present in another set?

Suppose I have this database scheme:

schema

I've been stumbling on this question for more than one hour:
"Select all albums of which no track has been used in a playlist".

I want to do something like this:

SELECT parentalbum.albumid FROM album AS parentalbum
INNER JOIN track
ON track.albumid = parentalbum.albumid
INNER JOIN playlistitem
ON track.trackid = playlistitem.trackid // Join the 3 tables
WHERE NOT ((SELECT track.trackid FROM album
INNER JOIN track
ON album.albumid = track.albumid
WHERE track.albumid = parentalbum.albumid ) // Select songs from one album
IN playlistitem.trackid ) // Check if at least one element of the album is in a playlist? (faulty)


My query is probably completely wrong, so I would appreciate any help.

EDIT: I forgot to mention, I am asked to solve this using correlated subqueries. Thank you!

Answer

NOT EXISTS() is the answer:

SELECT *        -- Select all albums
FROM album a
WHERE NOT EXISTS (      -- of which no track
    SELECT * FROM track t
    WHERE t.albumid = a.albumid
    AND EXISTS (
        SELECT * FROM playlistitem pi   -- has been used in a playlist
        WHERE pi.trackid = t.trackid    
        )
    )
        ;

Even simpler:

SELECT *        -- Select all albums
FROM album a    
WHERE NOT EXISTS (      -- of which no track
        SELECT * 
        FROM track t    -- has been used in a playlist
        JOIN playlistitem pi ON t.trackid = pi.trackid
        WHERE t.albumid = a.albumid 
        )
        ;
Comments