CODEWITHSUNDEEP
sql
JimmyNeutron
JimmyNeutron

Reputation: 43

Inner join 2 tables into another one

SELECT film.title, actor.last_name
FROM actor, film
INNER JOIN film_actor ON film.film_id = film_actor.film_id
    AND actor_id = film_actor.actor_id

This is the code I currenly have, I am using the sakila test database, basically I wish to show the film title and the actors last name but it doesnt work. The film_actor table contains the ids of which actor performed in what film so I want to combine both ids into that and show it. The book I'm reading doesn't explain how to combine 2 tables into one just one into another. I know the column with the films will repeat with each actor but that doesn't matter right now, I wish to solve this problem first

Upvotes: 0

Views: 268

Answers (1)

johna
johna

Reputation: 10772

You need two separate ON clauses for each join:

SELECT film.title, actor.last_name
FROM actor
INNER JOIN film_actor ON actor.actor_id = film_actor.actor_id
INNER JOIN film ON film.film_id = film_actor.film_id

Or an alternate syntax not using JOINs:

SELECT film.title, actor.last_name
FROM actor, film, film_actor
WHERE film.film_id = film_actor.film_id AND actor.actor_id = film_actor.actor_id

Upvotes: 1

Related Questions