I have two tables: 'movies' and 'users'. There's an n:m relationship between those, describing what movies a user has seen. This is described with a table 'seen' Now i want to find out for a given user, all the movies he has not seen. My current solution is like this:
SELECT *
FROM movies
WHERE movies.id NOT IN (
SELECT seen.movie_id
FROM seen
WHERE seen.user_id=123
)
This works fine but does not seem to scale very well. Is there a better approach to this?
See Question&Answers more detail:os