在SQL中加入3个不同的表

I am working on three different tables that are related for actors, movies they played in and what they were cast as in the movie. My current problem is I cannot get the Movie title to correlate correctly with the Actors Name and the role they played. Here is my SQL syntax. And a screenshot of my output. Obviously the movie title does not match with the actor. For instance Robert Loggia and AlPacino should both have the movie Scarface next to them. What am I missing here?

我正在制作三个不同的表格,这些表格与演员,他们演奏的电影以及他们在电影中扮演的角色有关。我目前的问题是我无法让电影标题与演员姓名及其扮演的角色正确关联。这是我的SQL语法。以及我输出的截图。显然电影片名与演员不匹配。例如Robert Loggia和AlPacino都应该在他们旁边放置电影Scarface。我在这里想念的是什么?

SELECT a.fname, lname, c.characterRole, m.title, salary
FROM Actor a, Castings c, Movie m 
where a.actorID = c.actorId and a.actorID = m.movieId

2 个解决方案

#1

First, never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax.

首先,永远不要在FROM子句中使用逗号。始终使用正确,明确,标准的JOIN语法。

The query you want looks like:

您想要的查询如下所示:

SELECT a.fname, lname, c.characterRole, m.title, salary
FROM Actor a JOIN
     Castings c
     ON a.actorID = c.actorId JOIN
     Movie m 
     ON c.movieId = m.movieId;

Equating actorId and movieId doesn't make sense. Movie ids should be connected to movie ids.

等同于actorId和movieId是没有意义的。电影ID应该连接到电影ID。

#2

I assume that the Castings table has a movieId to be linked to the Movie table

我假设Castings表有一个要链接到Movie表的movieId

SELECT 
a.fname AS FirstName, 
a.lname AS LastName, 
c.characterRole, 
m.title, 
c.salary
FROM Movie m
JOIN Castings c ON c.movieId = m.movieId
JOIN Actor a ON a.actorID = c.actorId

#1