How can I get the following result set from mysql sakila database: List of all the films with film name and name of every actor in that film

Question

I wrote the following query :

select film_id, actor_id
from sakila.film_actor
where film_id IN (
select film_id from sakila.film
)

which returns

film_id actor_id
1       1
1       10
1       20
....
2      19
2     85

But I also want to include the name of the film and the name of the actor in this result set.

The film title is in film table and the actor first and last name is the actor table.

Here is how the tables are structured :

Table         Columns
actor          actor_id, first name, last name
film           film_id, title
film_actor     actor_id, film_id(foreign keys to film and actor table)

I tried the following query :

select ac.actor_id, ac.first_name, ac.last_name, fm.title  from sakila.film_actor as fa  inner join sakila.actor ac on fa.actor_id = ac.actor_id   inner join sakila.film fm on fa.film_id = fm.film_id;

But this returns list of all films for every actor and I want the other way round. All actors in each film

Answer 1

Use an inner join

select f.film_id, a.actor_id, f.film_name, a.first_name, a.last_name
from sakila.film_actor a 
inner join sakila.film f
   on a.film_id = f.film_id

You'll have to use the actual field names from your schema, of course. (I just guessed film_name, first_name, and last_name.)