anoldmaninthesea
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"));
```
?
Edit: I tried my way, but it just returned an empty set, instead a 2-row table like in the book.
Top Answer
Jack Douglas
The second `film_actor` join is required because in each case it is being restricted to a single actor. The query is to get films where two actors appear together.
It's perhaps slightly clearer what's happening in this transformation of your query:
<>https://dbfiddle.uk/?rdbms=mysql_8.0&sample=sakila&fiddle=e96c0765b0d337328fe0e570896ee0b5
Logically you can do the same thing without joining `film_actor` twice:
<>https://dbfiddle.uk/?rdbms=mysql_8.0&sample=sakila&fiddle=d531515ee337dc2c7d3a12e576fe2592
But don't assume this will be faster, RDBMS's were born to `join`.