SQL: Select only rows fullfilling conditions via relation table

I have two tables and I need to select values from first only when all certain conditions are met in second table. Let explain more on an example.

First table

id       movie    
---|--------------
1  | Matrix       
2  | Pulp Fiction 
3  | Avengers     
4  | Commando

Second table

id    movie_id    user_id
---|-----------|---
1  | 1         |  1
2  | 1         |  2
3  | 1         |  3
4  | 2         |  1
5  | 2         |  4
6  | 3         |  2
7  | 4         |  1
8  | 4         |  3

And from those tables I need to find out only movies, that user 1 and 3 have seen. So in result I need to see

Wanted result

id    movie   
---|--------
1  | Matrix      
4  | Commando

I tried some queries, but cannot get a grasp of final result. In the end I will be “construncting” this query based on users selected as input. So in the end, there might be as well 5 users and I will need to find only movies, all of them have seen. So please bear that in mind.

All help and ideas are appreciated, thanks.

Answer

You can try below –

select b.id, b.movie from 
Secondtable a inner join Firsttable b 
on a.movie_id=b.id 
where user_id in (1,3)
group by b.id, b.movie
having count(distinct user_id)=2

Advertisement

Answer