获取自上而下的层次结构中的值,但请确保您在正确的域中进行搜索。获取所需的第一名称,但在取决于person_id的正确域中搜索该名称,而该域又取决于movie_id。最后,要调用该条件,我们必须重新调用people.id,因为条件取决于people表中的数据。必须在每个步骤中执行所需的JOIN。
要从结果中删除Kevin Bacon,可以使用EXCEPT关键字,并在其他查询中专门选择他。
SELECT name FROM people WHERE people.id
IN
( SELECT person_id FROM stars JOIN movies ON movies.id = stars.movie_id
WHERE movie_id IN
( SELECT movie_id FROM movies JOIN stars ON stars.movie_id = movies.id JOIN people ON
people.id = stars.person_id WHERE people.id IN (
SELECT id FROM people WHERE people.name = "Kevin Bacon" AND people.birth = 1958 )))
EXCEPT
SELECT name FROM people WHERE people.name = "Kevin Bacon" AND people.birth = 1958