显示来自两个不同表的所有列

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

我在MovieDB中有两个表，movies和movie_actors。电影的id是主键，movie_actor是foreign key, movies_actor是movies_actor。id被引用到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.

我使用了Union，但它不起作用，因为Union需要相同数量的属性。但是这里第一个表有4列而第二个只有3列。

I would like to display as in one table

我想在一个表格中显示

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

any ideas?

什么好主意吗?

2 个解决方案

#1

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:

您将希望在表上使用连接。连接将位于两个字段之间，这两个字段是主键/外键，您所声明的是电影表中的id和movie_actor表中的id:

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:

内部连接将返回两个表中匹配的记录集。如果在“move_actor表”中没有匹配记录的电影表中有记录，那么您将希望使用左连接。这将返回所有电影，即使它在movie_actor表中没有记录:

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.

这两个查询的演示请参见SQL Fiddle。

#2

You can join these tables using the following query.

您可以使用以下查询来连接这些表。

SELECT MOVIES.id, title, director, genre, year_of_release, actor, age
    FROM MOVIES INNER JOIN MOVIE_ACTOR ON (MOVIE.id = MOVIE_ACTOR.id)

#1