SQL Sakila Query Question - Find all actors that have starred in films of all 16 film categories

Question

I am trying to put together a query from the Sakila database.

The query should find all actors that have starred in all 16 film categories.

To get all of this information into one table for querying, I have performed a INNER JOIN:

SELECT a.first_name, a.last_name FROM actor a
INNER JOIN film_actor fa
ON fa.actor_id = a.actor_id
INNER JOIN film_category fc
ON fc.film_id = fa.film_id;

However, from there I do a GROUP BY on the category_id but don't know how to iterate through and count if a particular actor_id has all 16 categories?

Does this complex of a query require writing a FUNCTION or PROCEDURE?

Answer 1

You are almost there. Group against the actor name and check that the unique category count is 16:

SELECT a.actor_id, a.first_name, a.last_name
FROM actor a
  INNER JOIN film_actor fa ON fa.actor_id = a.actor_id
  INNER JOIN film_category fc ON fc.film_id = fa.film_id
GROUP BY a.actor_id, a.first_name, a.last_name 
HAVING COUNT(DISTINCT fc.category_id) = 
  ( 
    SELECT COUNT(DISTINCT category_id) 
    FROM film_category
  )