0

In my website, I have a users table, primary key is username. I have an albums table with an album id and an album title. I have also a table user_albums that models the relationship of many users like many albums.

This is a select query that returns the albums a particular user likes:

SELECT e.album_title
FROM user_albums d
INNER JOIN albums e ON d.album_id = e.album_id
WHERE d.user_id = $user

What I want is the same query, but I want it to return all the albums he does not like. So basically, select all albums not in that query.

What would I need to change to do this?

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
jmasterx
  • 50,457
  • 91
  • 295
  • 535

1 Answers1

2

This translates to a NOT EXISTS, "this user's id is not listed in the user_albums":

SELECT e.album_title FROM albums e 
WHERE NOT EXISTS
 ( SELECT * FROM user_albums d 
    WHERE d.user_id = '$user'
      AND d.album_id = e.album_id
 )

If you don't want to use a subquery the common way is to use an OUTER JOIN/IS NULL:

SELECT e.album_title 
FROM user_albums d 
LEFT JOIN albums e 
ON d.album_id = e.album_id 
AND d.user_id = '$user' -- when there's no match a NULL will be returned
WHERE d.album_id IS NULL; -- find that NULL
dnoeth
  • 57,618
  • 3
  • 33
  • 50
  • Is there a way to do it without a subquery. – jmasterx Feb 16 '15 at 19:00
  • @Milo. Such query is usually easier to read with a subquery. EXISTS uses a correlated subquery, i.e. you reference the outer table in that subquery. You can do the same with a non-correlated subquery with IN: `WHERE album_id NOT IN (select album_id from user_albums where user_id = '$user')`, which I consider even more readable. – Thorsten Kettner Feb 16 '15 at 20:55
  • 1
    @Thorsten Kettner: Of course NOT IN is easier to read, but NOT EXISTS works regardless of possible NULLs :-) – dnoeth Feb 16 '15 at 21:07