mysql add tag
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`.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.