Misohappi Misohappi - 4 months ago 18
MySQL Question

SQL query returning elements from multiple tables that passes certain conditions

Given these tables:

movie (id, name, year)
actor (id, fname, lname)
cast (movieid, actorid, role)

I want to find the names of actors that had 3 or more distinct roles in the same movies in 2013. I need the actors name, movie names, and the number of roles they had in that movie.

So far I have:

select a.fname, a.lname (select m.name (select count(distinct c.role)
from actor a, cast c
where a.id=c.actorid and
c.movieid=m.id and m.year =
2013 )
from movie m)
group by a.fname, a.lname
having count(distinct c.roles) >=3

My intuition is that the inner most query will compute the number of roles each actor played for a movie in 2013. The next level query will select the name of those movies and the outermost will return the name of the actors that had 3 or more distinct roles in each movie.

For example:


id name year
1 a 1990
2 b 2013
3 c 2000
4 d 1998
5 e 2013


id fname lname
1 bob dylan
2 angelina jolie
3 meghan fox
5 jane doe


actorid movieid role
1 2 nanny
1 2 maid
1 2 butler
2 3 main
1 5 baby
1 5 kid
1 5 teen
4 2 main
5 4 support

the query should return the following:

a.fname a.lname m.name count(distinct roles in movie)
bob dylan a 3
bob dylan e 3

There may be a actor that had 3 or more distinct roles in multiple movies in the year 2013. So bob played 3 roles in movie a and 3 roles in movie e both of which are from 2013, the results should list bob twice once with movie a and count of 3 and another with movie e count of 3.


This query should work:

SELECT actor.fname, actor.lname, movie.name, count(role)
FROM cast
JOIN movie ON movieid = movie.id
JOIN actor ON actorid = actor.id
WHERE movie.year = 2013
GROUP BY actorid, movieid
HAVING count(role) >= 3;

I hope it does. It seems to do now.