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.