SQL Movie Database query

Vote count:

0

I have this table:

actors(id: int, first_name: string, last_name: string, gender: string)
directors(id: int, first_name: string, last_name: string)
directors genres(director id: int, genre: string, prob: 
float)
movies(id: int, name: string, years: int, rank: 
float,)
movies directors(director id: int, movie id: int)
movies genres(movie id: int, genre: string)
roles(actor id: int, movie id: int, role: string)

I want to find the list of actors who have acted in all the comedy movies directed by Wilson Yip in the year 2002.

I am doing the following:

select distinct A.first_name, A.last_name, M.name
from actors A inner join roles R on A.id=R.actor_id
inner join movies M on M.id=R.movie_id
where M.name 
in (select M.name
from movies M 
inner join movies_genres G on M.id = G.movie_id
inner join movies_directors MD on M.id = MD.movie_id
inner join directors D on D.id = MD.director_id
where G.genre = 'Comedy' and M.year = 2002 and D.first_name='Wilson' and D.last_name='Yip');

But, this gives me list of actors who have acted in a comedy movie directed by Wilson in 2002, but I want actors who have acted in 'all' such comedies.

How to do that?

asked 34 secs ago

Linda Nu

13

SQL Movie Database query