0

This very nested query is to return names of all the actors that performed on a Kevin Bacon film. Instead it's returning nothing, any ideas are appreciated!

Note: I'm yet to implement the part that excludes Bacon himself from the final results. Please see below code for schema.

    SELECT name FROM people WHERE id IN(SELECT person_id FROM stars WHERE movie_id in
    (SELECT id FROM movies WHERE title in(SELECT movie_id FROM stars WHERE person_id in
    (SELECT id FROM people WHERE name = 'Kevin Bacon' AND birth = '1958'))));

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

Found it! (SPOILER WARNING)

This line doesn't refrence a key value but a 'regular' column value and therefore can't refrence to another table:

SELECT id FROM movies WHERE title in(SELECT movie_id FROM stars WHERE person_id---.

Instead it should refrence the proper key SELECT id FROM movies WHERE id in(SELECT movie_id FROM stars WHERE person_id---.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .