CODEWITHSUNDEEP
mysqlsql
FRIDI Mourad
FRIDI Mourad

Reputation: 87

all movies whatched by 2users in sql

I have a table rating(ID_User,ID_Movie,Rating). I want to extract all movies watched by 2 users. I have this query but she return always emplty.

SELECT 'ID_ Movie' FROM rating WHERE ('ID_User'='"+user1+"') and  'ID_ Movie' IN (select 'ID_ Movie' from rating where 'ID_User'='"+user2+"');

Upvotes: 0

Views: 78

Answers (1)

xQbert
xQbert

Reputation: 35323

A list of movie_ID's from Rating for two users where both users have rated the movie.

Select ID_MOVIE
FROM Rating
Where ID_user in ('USER1','USER2')
group BY ID_MOVIE
having count(Distinct ID_USER) = 2

What this does is return the movies rated by each of two users and then counts the Unique users who rated the movie. If the count = 2 then 2 users rated the movie.

Upvotes: 3

Related Questions