title: SQL with Multiple Left Joins
body: <p>Using: <a href="http://dev.mysql.com/doc/sakila/en/" rel="nofollow">http://dev.mysql.com/doc/sakila/en/</a></p>
  
  <p>I'm trying to write a query to count the number of films each actor has acted in by category.</p>
  
  <p>Would appreciate any pointers or advice.</p>
  
  <p>Here is my code so far (it counts the films correctly for one category when I take out the second LEFT JOIN section):</p>
  
  <pre><code>SELECT actor.first_name, actor.last_name, 
  COUNT(subset.film_id) AS action,
  COUNT(subset2.film_id) AS animation
  FROM actor
  LEFT JOIN (
  SELECT film.film_id, actor.actor_id 
  FROM actor
  INNER JOIN film_actor
  ON film_actor.actor_id = actor.actor_id
  INNER JOIN film 
  ON film.film_id = film_actor.film_id
  INNER JOIN film_category 
  ON film_category.film_id = film.film_id
  INNER JOIN category 
  ON category.category_id = film_category.category_id
  WHERE category.name = 'Action') AS subset 
  ON subset.actor_id = actor.actor_id
  LEFT JOIN (
  SELECT film.film_id, actor.actor_id 
  FROM actor
  INNER JOIN film_actor
  ON film_actor.actor_id = actor.actor_id
  INNER JOIN film 
  ON film.film_id = film_actor.film_id
  INNER JOIN film_category 
  ON film_category.film_id = film.film_id
  INNER JOIN category 
  ON category.category_id = film_category.category_id
  WHERE category.name = 'Animation') AS subset2
  ON subset2.actor_id = actor.actor_id
  GROUP BY actor.actor_id
  ORDER BY actor.last_name ASC;
  </code></pre>