子查询有许多很棒的方法,但是看来在本教程中您仅使用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)和演员表(作为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”关键字是可选的。我在上面插入了它,以帮助查询更有意义