Search code examples
mysqlsqlactormovie

SQL IMDB website query to find actors starred in at least 10 movies

Using the ER diagram of IMBD I need to find the time period in which each actor was active, by listing the earliest and the latest year in which the actor starred in a film, but only for the actors that have starred in at least 10 movies.

I wrote the part in regards to the period of acting, but am struggling with at least 10 movies one. I understand I should use HAVING COUNT

My answer so far is: 

SELECT r.actor_id, min(m.year), max(m.year) 
FROM roles r 
LEFT JOIN movies m ON r.movie_id = m.id 
GROUP BY r.actor_id

IMBD ER Diagram

Solution

  • Try the following. As pointed out my Barmar you don't need the left join.

    SELECT r.actor_id, min(m.year), max(m.year) 
FROM roles r 
GROUP BY r.actor_id
Having count(*) >= 10

    In case if you ever have to change the table structure for roles to include the scenario of a single actor performing multiple roles then you might have to change your query like below:

    SELECT r.actor_id, min(m.year), max(m.year) 
FROM roles r 
GROUP BY r.actor_id
Having count(distinct r.movie_id) >= 10