我在cs50 pset 7哪里出错了?SQL嵌套查询?

Question

我正在查找所有执导过至少获得9.0分的电影的人的名字这些表的模式是

CREATE TABLE movies (
id INTEGER,
title TEXT NOT NULL,
year NUMERIC,
PRIMARY KEY(id)
);
CREATE TABLE stars (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE directors (
movie_id INTEGER NOT NULL,
person_id INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id),
FOREIGN KEY(person_id) REFERENCES people(id)
);
CREATE TABLE ratings (
movie_id INTEGER NOT NULL,
rating REAL NOT NULL,
votes INTEGER NOT NULL,
FOREIGN KEY(movie_id) REFERENCES movies(id)
);
CREATE TABLE people (
id INTEGER,
name TEXT NOT NULL,
birth NUMERIC,
PRIMARY KEY(id)
);

MY SQL QUERY IS:

SELECT DISTINCT name FROM people 
WHERE id IN ( SELECT person_id FROM directors WHERE movie_id IN ( 
SELECT id FROM movies WHERE id IN (
SELECT movie_id FROM ratings WHERE votes >= 9.0)));

然而，这不能通过check50测试，并给出不正确的输出。有人能告诉我我哪里出错了吗?

Answer 1

规范说(强调添加)

在10.sql中，编写一个SQL查询，列出所有执导过获得评级的电影的人的名字

查询不过滤ratings.rating列。

Answer 2

试试这个:

select distinct name 
from directors 
join people on directors.person_id = people.id 
join ratings on directors.movie_id = ratings.movie_id 
where rating >= 9.0;