CODEWITHSUNDEEP
mysqlsql
123
123

Reputation: 8951

SQL - Find the first name, last name and total combined film length of Sci-Fi films for every actor

I have a query that accomplishes this, but it excludes actors who have never been in a Sci-Fi film. I want to include those actors even though their total length of Sci-Fi films is zero.

The result should list the names of all of the actors(even if an actor has not been in any Sci-Fi films) and the total length of Sci-Fi films they have been in.

I have the following query, I just don't know how to modify it to include all actors.

SELECT actor.first_name, actor.last_name, SUM(film.length)
FROM actor
INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
INNER JOIN film on film_actor.film_id = film.film_id
INNER JOIN film_category ON film_actor.film_id = film_category.film_id
INNER JOIN category ON film_category.category_id = category.category_id
WHERE category.name = 'Sci-Fi'
GROUP BY category.name, actor.actor_id
ORDER BY actor.first_name

I'm using the following schema:

Upvotes: 0

Views: 2453

Answers (2)

Abdullah Nehir
Abdullah Nehir

Reputation: 1047

You should change the join order and use a right join with actor table instead of inner join:

SELECT actor.first_name, actor.last_name, SUM(film.length)
FROM category
INNER JOIN film_category ON film_category.category_id = category.category_id and category.name = 'Sci-Fi'
INNER JOIN film on film.film_id = film_category.film_id
INNER JOIN film_actor ON film.film_id = film_actor.film_id
RIGHT JOIN actor ON actor.actor_id = film_actor.actor_id
GROUP BY actor.first_name, actor.last_name
ORDER BY actor.first_name

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

Besides switching to Outer Joins you might also use conditional aggregation (which can be easily extended to include other categories, too):

SELECT actor.first_name, actor.last_name,
   SUM(case when category.name = 'Sci-Fi' then film.length else 0 end)
FROM actor
INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
INNER JOIN film on film_actor.film_id = film.film_id
INNER JOIN film_category ON film_actor.film_id = film_category.film_id
INNER JOIN category ON film_category.category_id = category.category_id
GROUP BY category.name, actor.actor_id
ORDER BY actor.first_name

This will exclude actors who never made a film.

Upvotes: 2

Related Questions