Including 0 On account when filtering by SQL
Below is my code. It returns a count of each category the person was in for the movie. It returns a result, but I would like it to list every category, including those with 0 counters. I've tried every combination of LEFT JOIN, OUTER JOIN, etc. and it still doesn't work. Any help would be appreciated!
SELECT c.name, COUNT(f.title) FROM category c
LEFT JOIN film_category fc ON c.category_id = fc.category_id
INNER JOIN film f ON fc.film_id = f.film_id
INNER JOIN film_actor fa ON f.film_id = fa.film_id
INNER JOIN actor a ON fa.actor_id = a.actor_id
WHERE a.first_name = 'Jack' AND a.last_name = 'Daniel'
GROUP BY c.name ASC;
+3
source to share
2 answers
Try it. Demo SQL Fiddle
SELECT c.name, COUNT(f.title) FROM category c
LEFT JOIN film_category fc ON c.category_id = fc.category_id
LEFT JOIN film f ON fc.film_id = f.film_id
LEFT JOIN film_actor fa ON f.film_id = fa.film_id
LEFT JOIN actor a ON fa.actor_id = a.actor_id and a.first_name = 'Jack' AND a.last_name = 'Daniel'
WHERE 1=1
GROUP BY c.name ASC;
+1
source to share