-1

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?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user2518312
  • 17
  • 4

1 Answers1

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
  )
slaakso
  • 8,331
  • 2
  • 16
  • 27
  • Probably should add a `WHERE fc.category_id IN (...)` to limit the matches to the 16 categories desired, in case there are other categories added in the future. – Bill Karwin Jan 31 '23 at 19:23
  • 1
    @BillKarwin futureproofed the answer – slaakso Jan 31 '23 at 19:28
  • Fair enough. It depends on whether the requirement is to check for a specific 16 categories, or to check that all categories are matched. – Bill Karwin Jan 31 '23 at 19:31
  • @BillKarwin "all 16 film categories" The content of the Sakila database has not changed since 2006 and is not likely to change in the future either. – slaakso Jan 31 '23 at 19:39
  • @user2518312 remove the having and add COUNT(DISTINCT fc.category_id) to what's selected, and you will see it only reports 3 in that case (so the having clause will filter out that row) – ysth Jan 31 '23 at 19:47
  • ello! Thanks for the help! That makes sense to me. I also have in mind the case where you have 16 entries where actor_id = 1, film_id = a variety of numbers but category_id = 1,3, or 5 but not the other 16 categories? In this case, would it require a procedure or function being written? UPDATE: just looked over the query again and realized that HAVING COUNT(DISTINCT fc.category_id) would automatically weed out this case described in this comment. Thanks alot!!!!! – user2518312 Jan 31 '23 at 19:59