INNER JOIN in SQL with 4 tables

Question

INNER JOIN in SQL with 4 tables

Navigation

#1 by (2 votes)

1

I try to do INNER JOIN in SQL with 4 tables, but, it does not work out.

I use this SQL Query (this works):

SELECT PELICULAS.*, companias.nombre AS nombreCompania
FROM companias INNER JOIN peliculas ON companias.id_compania = peliculas.id_compania

But, when I add another INNER JOIN to enter the table PELICULAS_DIRECTORES does not work:

SELECT PELICULAS.*, companias.nombre AS nombreCompania,
DIRECTORES.nombre AS nombreDirector
FROM companias INNER JOIN peliculas ON companias.id_compania = peliculas.id_compania
FROM DIRECTORES INNER JOIN PELICULAS_DIRECTORES ON DIRECTORES.id_director = PELICULAS_DIRECTORES.id_director

Diagram:

The first Query works and shows me all the PELICULAS and I exchange the id_compania for the nombre of the table COMPANIAS .

I want to do the same and get the same, but now, exchanging the id_director of the table PELICULAS_DIRECTORES with the nombre of the table DIRECTORES . I mean, I want to show at the end the "id", "titulo", "anoLanzamiento", "longitudMinutos", "sinopsis", "nombreCompania", "nombreDirector"

I have tried many combinations and it does not work out, what's wrong with my second Query ?.

sql

asked by Robert Gomez 18.11.2017 в 22:30

source

1 answer

How to make a scroll on the same page when selecting a Radio Button option? How can I see my laravel project in a local network

score 2 · Accepted Answer

In a query SELECT , a single FROM is set and then the INNER JOIN with its respective ON joining the ids of the relational tables. Putting the tables together would be like this:

SELECT p.id_pelicula, p.titulo, p.anoLanzamiento, p.longitudMinutos, p.sinopsis, 
c.nombre AS nombreCompania, 
d.nombre as nombreDirector 
FROM PELICULAS_DIRECTORES AS pd
INNER JOIN PELICULAS AS p ON p.id_pelicula = pd.id_pelicula
INNER JOIN DIRECTORES AS d ON d.id_director = pd.id_director
INNER JOIN COMPANIAS AS c ON p.id_compania = c.id_compania;