Sample DVD Rental database, find a customers favorite actor
Sample DVD Rental database, find a customers favorite actor I've been trying to improve my SQL join skills. I'm using the classic sample DVD Rental Database (can be found here ). I am trying to determine a customers favorite actor, by counting up all appearances the actor has appeared in all the movies the customer has rented. Right now I have this monster query that has 3 sub queries. SELECT email, actor.last_name, count(actor.last_name) FROM (SELECT email, actor_id FROM (SELECT email, film_id FROM (SELECT email, inventory_id FROM customer as cu JOIN rental ON cu.customer_id = rental.customer_id ORDER BY email) as sq JOIN inventory ON sq.inventory_id = inventory.inventory_id) as sq2 JOIN film_actor ON sq2.film_id = film_actor.film_id) as sq3 JOIN actor ON sq3.actor_id = actor.actor_id GROUP BY email, actor.last_name ORDER BY COUNT(actor.last_name) DESC; And what I end up getting is the full list of email...