3

So we have the following relational schema:

users(user_id, ..)
movies(movie_id, ..)
ratings(user_id, movie_id, rate)

So, we want to find all the users who have rated all the movies which are rated by a specific user '1234'

In other words, if user '1234' has rated movies 1, 2, 3, and 4, we want the user ID of all others who have also rated movies 1, 2, 3, 4.

2
  • Do you want to exclude users that has also rated movie 5, i.e. a user who rated movie 1,2,3,4 and 5? This type of query is known as relational division Mar 14, 2016 at 6:52
  • Are you looking for other users who rated all of those movies, at least one or those movies, or, as Lennart asked, exactly the same (and only those) same movies?
    – Joel Brown
    Mar 14, 2016 at 12:13

2 Answers 2

1

This will not suffer from duplicate votes

SELECT user_id
FROM ratings 
WHERE movie_id IN (SELECT movie_id FROM ratings WHERE user_id = '1234') AND user_id <> '1234'
GROUP BY user_id 
HAVING count(DISTINCT movie_id) = (SELECT count(DISTINCT movie_id) FROM ratings WHERE user_id = '1234')
0

This should work:

select
     ratings.user_id
from ratings 
join movies on movies.movie_id = ratings.movie_id
join ratings l on l.movie_id = movies.movie_id and l.user_id = '1234'
cross join (
    select count(*) as num
    from ratings 
    where raings.user_id  = '1234'
) c
group by ratings.user_id
having count(*) = c.num

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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