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

Question

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 1

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.

Answer 2

considering a n:m relationship between Users and Movies table, with intermediate table Seen.

SELECT * FROM Users u WHERE NOT EXISTS (SELECT UserId FROM Seen s WHERE s.UserId = u.ID)

this query will return Users which does not have any related record in Seen table

Answer 3

I would say something like a left-joining the users-table to the seen-table (and join that table to the movies-table).

(edited the code due to a comment from MatBailie)

Add the list-restritctions in the JOIN-clause (and not the WHERE-clause as MatBailie pointed out to me) and you get something like (Code below should work on SQL-Server but something similar should work for MySql as well):

SELECT COUNT(Users.ID)
FROM Users
LEFT JOIN Seen ON Users.ID = Seen.UserID AND Users.something IN (list)
LEFT JOIN Movies ON Seen.MovieID = Movie.ID AND Movies.something IN (list)
WHERE Movies.ID IS NULL
GROUP BY User.ID -- <-- This is probably optional

But as there are usually multiple ways to get the same result, the adjusted version of my previous answer:

SELECT COUNT(Users.ID)
FROM Users
LEFT JOIN Seen ON Users.ID = Seen.UserID
LEFT JOIN Movies ON Seen.MovieID = Movie.ID
WHERE (Users.something IN (list) OR Users.something IS NULL)
  AND (Movies.something IN (list) OR Movies.something IS NULL)
  AND Movies.ID IS NULL
GROUP BY User.ID -- <-- This is probably optional

Third attempt: Get all user-ids in list that have seen one of the movies. Next, get all user-ids in list and subtract the user-ids that have seen one of these movies. Know that for large data sets (a couple of thousand is not large) this query might become slow. To test it I've removed "userid = 2 and movieid = 3" from the list of seen, else I would not get a result. Now I see that Nick has not seen any of the first three movies (referring to your rextester example)

SELECT *
FROM musers
WHERE musers.id IN (1,2,3)
  AND musers.id NOT IN (
    SELECT musers.id
    FROM musers
    JOIN Seen ON musers.ID = Seen.UserID 
    JOIN Movies ON Seen.MovieID = movies.ID 
    WHERE movies.id IN (1,2,3) )