0

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

apandey
  • 85
  • 1
  • 4
  • 13
  • Simply join the tables. This should be one of the first things you learn, when learning SQL, so I am a bit surprised you don't know about this. If you are learning with a book or tutorial, maybe just read the next few pages? – Thorsten Kettner Apr 17 '22 at 18:30
  • 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. – apandey Apr 17 '22 at 18:52
  • 1
    This is the same. If you show one row per film and actor, then "all films and all their actors" is the same as "all actors and all their films". If you are merely talking about the order in which the results are shown and you want them shown in another order, then use `ORDER BY`. – Thorsten Kettner Apr 17 '22 at 19:59
  • You are correct. I guess I was just missing the order by clause. Thanks! select fm.title, ac.actor_id, ac.first_name, ac.last_name 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 order by fm.title This query gets the result I want – apandey Apr 17 '22 at 20:13

1 Answers1

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.)

Graham Charles
  • 9,394
  • 3
  • 26
  • 41