Why does my SQL script select too many outputs (CS50 pset7 sql.13)?

Question

I am currently working on pset7 in cs50, and I thought I had selected the correct number of values specified, but my script is outputting 349 rows instead of the 176 the answer key has.

"In 13.sql, write a SQL query to list the names of all people who starred in a movie in which Kevin Bacon also starred. Your query should output a table with a single column for the name of each person. There may be multiple people named Kevin Bacon in the database. Be sure to only select the Kevin Bacon born in 1958. Kevin Bacon himself should not be included in the resulting list."

CS50 pset7 sql.13

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

MY SCRIPT

SELECT DISTINCT name

FROM 
    people
    INNER JOIN  stars ON people.id = stars.person_id
    INNER JOIN movies ON movies.id = stars.movie_id

WHERE movies.title IN (

SELECT
    title
    
From
    movies
    INNER JOIN stars ON movies.id = stars.movie_id
    INNER JOIN people ON stars.person_id= people.id
    
WHERE 
    people.name = "Kevin Bacon"
    AND
    people.birth = "1958"
    )
    
EXCEPT SELECT name FROM people WHERE people.name = "Kevin Bacon"

Are there some logical errors in this script? My logic was:

Select all movies Kevin Bacon stars in (nested SELECT)
Select names of stars (main SELECT) who appear in any of these Kevin Bacon movies, except Kevin Bacon himself.

First of all, your inner query should select `movies.id`, not `title`, since `id` is unique (while `title` may be not) and since `id` is enough to know which movie it is. Then you check `WHERE movie_id IN (...)` and furthermore you won't need to join `movies` in the outer query, because you already have `movie_id` from joining `stars` — Alexey S. Larionov, Aug 18 '20 at 19:30
Thanks for the help! If anyone uses this for help as well, note that when Alex stated to use `movie_id` you have to specify which table you are pulling the id from because `movie_id` is found in multiple tables. SQL doesn't know which one you are trying to use. In my case it was using ... `WHERE stars.movie_id IN (` Thanks again! — Deece5531, Aug 18 '20 at 21:13
Is it? If your outer query is `SELECT DISTINCT name FROM people INNER JOIN stars ON people.id = stars.person_id WHERE ...`, then `movie_id` comes only from `stars` so no ambiguity there. — Alexey S. Larionov, Aug 19 '20 at 10:58

Isolated · Accepted Answer · 2020-08-18T20:04:22.970

Something like this would work in postgres. May need to adapt to your database.

    select name
    from (
        with kb_movies as
            (select distinct movies.id as kb_movie_id
            from movies
            join stars 
                on stars.movie_id = movies.id
            join people 
                on people.id = stars.people_id
            where people.name = 'Kevin Bacon'
            and people.birth = '1958' --or 1958
            )
            select distinct people.name
            from people
            join stars 
                on stars.people_id = people.id
            join movies 
                on movies.id = stars.movie_id
            join kb_movies 
                on kb_movie_id = movies.id
        )z
    where name <> 'Kevin Bacon'

Why does my SQL script select too many outputs (CS50 pset7 sql.13)?

1 Answers1