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
  • 605,456
  • 145
  • 1,078
  • 1,228
jmasterx
  • 52,639
  • 96
  • 311
  • 557

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
  • 59,503
  • 4
  • 39
  • 56
  • 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