CS50 Pset7 Movies 10.sql查询失败：9.0+评分电影导演查询问题

问题背景 #

任务需求：找出所有执导过评分至少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)
        );

用户编写的SQL语句：

SELECT DISTINCT name FROM people
JOIN directors ON directors.person_id = people.id
JOIN movies ON movies.id  = directors.person_id
JOIN ratings ON ratings.movie_id = movies.id
WHERE ratings.rating >= 9.0;

遇到的问题：本地sqlite3执行该语句能返回结果，但check50测试提示'Query did not return results'。

错误原因 #

核心错误是关联movies表的条件逻辑错误：
原SQL中JOIN movies ON movies.id = directors.person_id，错误地将directors表的person_id（关联people表的id字段）与movies表的id做关联，这不符合表结构的关联逻辑。正确的关联应该是directors表的movie_id对应movies表的id——directors表的movie_id字段才是用来关联电影表的外键。

本地执行有结果是巧合：本地数据库中恰好存在人员id与电影id相同的记录，而check50的测试数据集里没有这类错误匹配的数据，因此返回空结果。

修正后的SQL语句 #

SELECT DISTINCT name FROM people
JOIN directors ON directors.person_id = people.id
JOIN movies ON movies.id = directors.movie_id
JOIN ratings ON ratings.movie_id = movies.id
WHERE ratings.rating >= 9.0;

内容的提问来源于stack exchange，提问作者Rha3L