user1221765 user1221765 - 3 months ago 10
SQL Question

Displaying all the columns from two different tables

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

id
of the
movie_actor
is foreign key, and
movies_actor.id
is referenced to movies.id

TABLE MOVIES
id/title/director/genre/year_of_release

TABLE MOVIE_ACTOR
id/title/actor/age


I used Union, but its not working, because union is asking for same number of attributes. but here the first table has 4 columns and the second has only 3.

I would like to display as in one table

id/title/director/genre/year_of_release/actor/age


any ideas?

Answer

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 movie_actor table:

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.

An 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 movie_actor table:

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.