Searching multiple rows that are not in a relationship n:m

Similar question here

Very similar to the question above but with a slight difference, I need to find a list of users that haven’t seen at least one film in a list of movies.

Assuming two tables ‘movies’ and ‘users’, there’s an n:m relationship between those, and a table ‘seen’ describing that relationship.

I need to find out for any number of given users, and any number of given movies all the users, from that given list, that have not watched at least one of the given movies.

Is this achievable in a single query? I can’t figure out a way of do that.

Edit: Here’s a demo with an attempt to solve the problem, the issue with that is it returns users that not have seen all of the movies from the given list. What we need is a user that has not seen ANY of the movies from that list: http://rextester.com/DEIH39789

Answer

This query should give you your desired result. I’m assuming your basic structure is:

users (id int, name varchar(20));
movies (id int, title varchar(20));
seen (user_id int, movie_id int);

SELECT u.*
FROM users u
LEFT JOIN seen s
ON s.user_id = u.id AND s.movie_id IN (movielist)
WHERE s.user_id IS NULL AND u.id IN (userlist)

The WHERE s.user_id IS NULL condition means the LEFT JOIN gives you all the users who have not seen any of the movies in movielist, and the u.id IN (userlist) then restricts the results to only that set of users. You would modify the IN clauses to match the list of movies and users you were interested in. I’ve made a small demo on Rextester.

Update

I had misinterpreted the question; the desired result is for users who have not seen one (or more) of the movies in the list. This query solves that problem:

SELECT u.*
FROM musers u
LEFT JOIN seen s
ON s.user_id = u.id AND s.movie_id IN (1, 2)
WHERE u.id IN (1, 2, 3)
GROUP BY u.id
HAVING COUNT(s.movie_id) < 2

The result of the JOIN and WHERE is users (1, 2, 3) and the movies they have seen. If they have seen all of the movies in the movie list (1, 2), the COUNT of movies in seen will be 2, otherwise, if they have not seen one (or more) it will be less than 2. Here’s an updated demo. Note that when the length of the movie list changes, the 2 in the HAVING clause must change to match the length of the movie list.