如何列出三向连接表查询中的第一个值？

好吧,我解释事情很糟糕,所以我先给你报价和链接:

问题4b(靠近底部):

4B.列出所有'朱莉安德鲁斯'电影的电影片名和主角.

电影(id,title,yr,score,votes,director)
actor(id,name)
cast(movieid,actorid,ord)
(注:movie.id = casting.movi​​eid,actor.id = casting.actorid)

我的回答(不起作用):
    SELECT title, name
      FROM casting JOIN movie
              ON casting.movieid = movie.id
      JOIN actor
              ON casting.actorid = actor.id
     WHERE name = 'Julie Andrews'
       AND ord = 1

这里的问题是,它希望电影的主要演员名单与'朱莉安德鲁斯'作为一个演员(谁不一定是主角),但我所做的所有我的答案是让电影在她的领导(ord = 1).

如何在没有"Julie Andrews"的情况下指定主要演员名单？我怀疑我必须对GROUP BY做些什么,但我现在无法弄清楚...

编辑:我需要使用嵌套的SELECT吗？

使用子查询可以很好地实现这一点,但是教程中的这一点似乎只适用于JOIN.以下是如何仅使用JOIN执行此操作:

SELECT
  movie.title,
  a2.name
FROM
  actor AS a1
  JOIN casting AS c1 ON (a1.id = c1.actorid)
  JOIN movie ON (c1.movieid = movie.id)
  JOIN casting AS c2 ON (movie.id = c2.movieid)
  JOIN actor AS a2 ON (c2.actorid = a2.id)
WHERE 
  a1.name = 'Julie Andrews'
  AND c2.ord = 1

编辑(更具描述性):

这将给我们一个包含Julie Andrews所扮演的所有电影的表格.我将演员和演员表别名分别为a1和c1,因为现在我们找到了电影列表,我们将不得不转而匹配再次反对铸造表.

SELECT
  movie.*
FROM
  actor a1
  JOIN casting c1 ON (a1.id = c1.actorid)
  JOIN movie ON (c1.movieid = movie.id)
WHERE
  a1.name = 'Julie Andrews'

现在我们有一个她所扮演的所有电影的列表,我们需要将其加入到转换表(如c2)和actor表(作为a2)以获取这些电影的主要角色列表:

SELECT
  movie.title,  -- we'll keep the movie title from our last query
  a2.name       -- and select the actor's name (from a2, which is defined below)
FROM
  actor a1                                     -- \
  JOIN casting AS c1 ON (a1.id = c1.actorid)   --  )- no changes here
  JOIN movie ON (c1.movieid = movie.id)        -- /
  JOIN casting AS c2 ON (movie.id = c2.movieid)  -- join list of JA movies to the cast
  JOIN actor AS a2 ON (c2.actorid = a2.id)  -- join cast of JA movies to the actors
WHERE 
  a1.name = 'Julie Andrews'  -- no changes
  AND c2.ord = 1    -- only select the star of the JA film

编辑:在别名中,'AS'关键字是可选的.我已将其插入上面以帮助查询更有意义