cs50 pset7 13.sql - remove kevin from results

Question

UPDATE: I was able to easily and simply solve this problem by changing 2 things:

1. Add to the end of the code: AND people.name != "Kevin Bacon"
2. Change nested query to movies.id instead of title.

Thanks to this thread for the answer: CS50 Pset 7 13.sql, I can't solve it, nested sqlite3 database

I'm at the very last step of PSET7 movies (https://cs50.harvard.edu/x/2020/psets/7/movies/) The goal output should be all people that have starred in a movie with Kevin, but Kevin himself should NOT be included.

I have a query that returns a list of all the names of people who starred in movies with Kevin Bacon, but Kevin is there. How can I remove him from the results easily? I've looked into NOT IN, but I can't figure out how to get that to work with nested queries the way I have set this up. So any advice is greatly appreciated.

Here's my code:

SELECT DISTINCT people.name
FROM people
INNER JOIN stars ON stars.person_id = people.id
INNER JOIN movies ON movies.id = stars.movie_id
WHERE movies.title IN (
SELECT DISTINCT movies.title
FROM movies
INNER JOIN stars ON stars.movie_id = movies.id
INNER JOIN people ON people.id = stars.person_id
WHERE people.name = "Kevin Bacon" AND people.birth = "1958");

Answer 1

Try

SELECT name 
FROM   people 
WHERE  id IN (SELECT DISTINCT person_id 
              FROM   stars 
              WHERE  movie_id IN (SELECT movie_id 
                                  FROM   stars 
                                         INNER JOIN people 
                                                 ON stars.person_id = people.id 
                                  WHERE  people.name = "kevin bacon" 
                                         AND people.birth = 1958)) 
       AND NOT ( name = "kevin bacon" 
                 AND birth = 1958 ); 

Explanation-

• First SELECT all movie_ids where "kevin bacon" (the one who was born in 1958) has starred
• Now SELECT all person_ids who've starred in the aforementioned movies
• Now SELECT the names of all these people
• Finally, exclude only "kevin bacon" (the one who was born in 1958) from this result