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.

Andriy M
  • 23,261
  • 6
  • 60
  • 103
Vahid Mirjalili
  • 131
  • 3

2 Answers2

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')
Сергей Черниенко
  • 111
  • 1
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
Pieter Geerkens
  • 2,008
  • 14
  • 21