CODEWITHSUNDEEP
mysqlnatural-join
user6417385
user6417385

Reputation:

Natural join of 3 tables in mysql

So i have 3 tables, movies(contains columns id and name), directors(id, first_name, last_name) and movies_directors(movie_id and director_id). I want to combine these 3 tables, since they have same values(director_id and movie_id) and then project specific columns. Thats what i did so far:

select first_name, last_name, name 
from movies natural join directors natural join movies_directors 
where movies_directors.movie_id=movies.id and  
movies_directors.director_id=directors.id;

However, the table that is produced in the end is empty! I have aslo tried to use views, and it works with either 2 tables(it returns the combined table) but when it comes to the third one the table returned is empty.

Upvotes: 1

Views: 4460

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521904

The natural join requires two tables invovled in the join to have a join column with identical names, which is not the case for your schema. Use an INNER JOIN instead with an explicit ON clause:

SELECT t1.name AS movie_name,
       t2.first_name,
       t2.last_name,
FROM movies natural t1
INNER JOIN directors t2
    ON t1.id = t2.movie_id
INNER JOIN movies_directors t3
    ON t2.id = t3.director_id

Upvotes: 1

Related Questions