How to display all the columns of two different tables as ONE ?
I have two tables, movies and movie_actors in MovieDB. The id of the movies is primary key and
You will want to use a join on the tables. The join will be between the two fields that are primary/foreign keys to each other which you stated was the
id in the
movie table and the
id in the
select m.id, a.title, m.director, m.genre, m.year_of_release, a.actor, a.age from movies m inner join movie_actor a on m.id = a.id
If you are not familiar with join syntax than here is great visual explanation of joins.
INNER JOIN will return the set of records that match in both tables. If you had records in the
movie table that did not have matching records in the
move_actor table, then you would want to use a
LEFT JOIN. This would return all movies even if it did not have records in the
select m.id, a.title, m.director, m.genre, m.year_of_release, a.actor, a.age from movies m left join movie_actor a on m.id = a.id
See SQL Fiddle with Demo of both queries.