pset7/movies/13.sql: exclude Kevin Bacon from the resulting list

Question

In pset7/movies, I want to dynamically exclude Kevin Bacon from the resulting list (e.g. not specifically mentioning his name with NOT or EXCEPT or copying and pasting code that calculates his ID, so that it works for any name, entered in one place, not hard-coded).

I'm guessing that I'd need to somehow save the result of a SELECT operation.

So, can I, for instance, save the result of this operation and use it later? I've tried AS and INTO but could never get the syntax right.

SELECT id FROM people WHERE name = "Kevin Bacon" AND birth = 1958

(If so, can this be done on a nested operation as well?)

Thanks in advance!

---

Update: I've found a way to deal with my problem, however CS50 doesn't accept it, since it requires multiple SQL statements (separated by ;)—I'm getting a "too many statements at once" error, so if anybody has a better solution (especially runtime-wise), you're more than welcome to share it.

One way to solve the problem is to create a table, for instance:

CREATE TABLE starID AS SELECT id FROM people WHERE name = "Kevin Bacon" AND birth = 1958;

and drop it afterwards:

DROP TABLE starID;

Answer 1

Try:

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