123 123 - 25 days ago 10
MySQL Question

SQL - Count of each category of film ED CHASE has been in

I'm trying to return a table with

category.name
and the count of the number of films that ED CHASE was in which were in that category. The query should return every category even if ED CHASE hasn't been in a film in that category.

This is the schema:


All I have so far is a way to select all category names. I haven't been able to tie this into ED CHASE in any way though.

SELECT category.name
FROM film_category
INNER JOIN category ON film_category.category_id = category.category_id,


I've tried to join these results with
film_actor
, but I keep getting syntax errors when trying to do that.

SELECT category.name
FROM film_category
INNER JOIN category ON film_category.category_id = category.category_id,
INNER JOIN film_actor ON film_category.film_id = film_actor.film_id

Answer

Try this:

SELECT c.category_id, c.name, COUNT(a.actor_id)
FROM category AS c
LEFT JOIN film_category AS fc ON c.category_id = fc.category_id
LEFT JOIN film AS f ON fc.film_id = f.film_id
LEFT JOIN film_actor AS fa ON f.film_id = fa.film_id 
LEFT JOIN actor AS a ON fa.actor_id = a.actor_id AND 
                        a.first_name = 'ED' AND a.last_name = 'CHASE'
GROUP BY c.category_id, c.name

The query will returns one record for each category even if this category has no films associated with it. You can change that and use an INNER JOIN to film_category in case you want only categories having at least one associated film.

Comments