0

Say I have a table of users, songs, and songs played.

users
id: int

songs
id: int

songs_played
user_id: int
song_id: int

If I want to, for each user, find a song that has not yet been played. How do I do that in one query?

It's pretty straightforward for me to just do something like:

user_ids = SELECT id FROM users;
for user_id in user_ids:
    SELECT *
    FROM songs AS s 
    WHERE s.id NOT IN (
        SELECT p.song_id
        FROM songs_played 
        WHERE id = user_id
    )
    ORDER BY RANDOM()
    LIMIT 1;

But that's pretty inefficient.

Thanks!

3

You can use not exists clause to do this.

SELECT
    u. ID AS user_id,
    s. ID AS song_id
FROM
    songs s,
    users u
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            user_songs us
        WHERE
            us.user_id = u. ID
        AND us.song_id = s. ID
    )
ORDER BY
    1,
    2
3

Another way would be to use EXCEPT like this:

SELECT
  u.id AS user_id,
  s.id AS song_id
FROM
  users AS u
  CROSS JOIN songs AS s

EXCEPT

SELECT
  user_id,
  song_id
FROM
  songs_played
;

The result would be all u.id, s.id pairs that are not found in songs_played.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.