cs50 – pset7 – movies – 10.sql failed check50 test

December 6, 2022

I’m currently working on 10.sql which asked me to find all people who directed a movie that received a rating of at least 9.0

Here’s the schema:

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)
        );

Here’s the code I wrote:

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;

When I worked on sqlite3, it returned me a list of names. But it failed the check50 test with error message of ‘Query did not return results’. I couldn’t figure out why. Can anyone tell me what I did wrong? Many thanks

>Solution :

Why do you need to join on movies? Try:

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