MySQL – Finding Films with Two Specific Actors in Sakila DB

MySQL

I'm following a book which states that if we wanted to show a table for the films where Cate MacQueen and Cuba Birch both appeared, I should run the following command:

select f.release_year, f.title, concat(a1.first_name," ", a1.last_name)
from film f
    inner join film_actor fa1
    on f.film_id=fa1.film_id
    
    inner join actor a1
    on fa1.actor_id=a1.actor_id
    
    inner join film_actor fa2
    on f.film_id=fa2.film_id
    
    inner join actor a2
    on fa2.actor_id=a2.actor_id
    
where ((a1.first_name="CATE" and a1.last_name="mcqueen") 
    and (a2.first_name="cuba" and a2.last_name="BiRcH"));

My question is why the need for the second inner join film_actor fa2 ? Why can't we just do

select f.release_year, f.title, concat(a1.first_name," ", a1.last_name)
from film f

    inner join film_actor fa1
    on f.film_id=fa1.film_id
    
    inner join actor a1
    on fa1.actor_id=a1.actor_id
    
    inner join actor a2
    on fa1.actor_id=a2.actor_id
    
where ((a1.first_name="CATE" and a1.last_name="mcqueen") 
    and (a2.first_name="cuba" and a2.last_name="BiRcH"));

?

I tried my way, but it just returned an empty set, instead a 2-row table like in the book.

Best Answer

In the first case you get a combination of all pairs of actors for each film. Try this query:

select f.film_id, fa1.actor_id, fa2.actor_id, concat(a1.first_name," ", a1.last_name), concat(a2.first_name," ", a2.last_name)
from film f
    inner join film_actor fa1
    on f.film_id=fa1.film_id
    
    inner join actor a1
    on fa1.actor_id=a1.actor_id
    
    inner join film_actor fa2
    on f.film_id=fa2.film_id
    
    inner join actor a2
    on fa2.actor_id=a2.actor_id   
where f.film_id = 82;

enter image description here

In the second case you just join the same actor details twice:

select f.film_id, fa1.actor_id, concat(a1.first_name," ", a1.last_name), concat(a2.first_name," ", a2.last_name)
from film f

    inner join film_actor fa1
    on f.film_id=fa1.film_id
    
    inner join actor a1
    on fa1.actor_id=a1.actor_id
    
    inner join actor a2
    on fa1.actor_id=a2.actor_id
where f.film_id = 82;

enter image description here
So there are no rows which correspond to your where conditions (a1.first_name="CATE" and a2.first_name="cuba")

Additional explanation:
SELECT * FROM film - you get the list of films (one row per film)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id - you get the list of actors for the film (multiple lines for each film and each line contains one actor id)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id JOIN actor a1 ON fa1.actor_id=a1.actor_id - you get the same rows as above but with additional details for each actor (each line is still related to only one actor, there is no pairs of actors)
SELECT * FROM film f JOIN film_actor fa1 ON f.film_id = fa1.film_id JOIN actor a1 ON fa1.actor_id=a1.actor_id JOIN actor a2 ON fa1.actor_id=a2.actor_id - the same results as above, you just joined the same details for the same actor twice

SELECT * 
FROM film f 
JOIN film_actor fa1 ON f.film_id = fa1.film_id
JOIN film_actor fa2 ON f.film_id = fa2.film_id

After the first join you get the list of actors for the film. And on the second join you add the same list of actors to each line from the above result and get the list of all pairs of actors for each film (as you can see on screenshot above)

Related Question