PSET7 Movies - Q13.SQL How to NOT include 'Kevin Bacon' in List of Actors that have starred in movies with 'Kevin Bacon

Question

I was able to determine the list of actors that have been in movies with 'Kevin Bacon' and get rid of the duplicates with DISTINCT; however, I cannot figure out how to exclude 'Kevin Bacon' from the list of actors.

Here is the SQL code I have so far that gives me a list of 177 actors including 'Kevin Bacon.

 SELECT DISTINCT(name) FROM people WHERE id IN
(SELECT person_id FROM stars Where movie_id IN
(SELECT movie_id FROM stars Where person_id IN
(SELECT id FROM people WHERE name IS 'Kevin Bacon' and 
(SELECT id FROM people WHERE birth = 1958))))
 ORDER BY name

I have tried using NOT, LIKE, IS, = and a few other terms in the ORDER BY area and all I get is his name at the top or bottom of the list.

I have tried inserting NOT, NOT LIKE, IS NOT, NOT =, != 'Kevin Bacon' in various ways with the above Keywords in the first SELECT DISTINCT name; after WHERE id; after Where person_id; and after the original name IS 'Kevin Bacon' but, nothing seems to remove his name from the list.

I have also googled many versions of remove name or item for SQL list and have not found any information there that corresponds to this issue.

If anyone has any ideas, please let me know. I am now attempting to change the whole code and add another row of [SELECT id FROM people WHERE name IS NOT 'Kevin Bacon'; but as I am getting the correct list with 177 names I am reluctant to tear i apart and start from scratch.

I have also reviewed the KEYWORDS and tried to use CASE CASE WHEN 'Kevin Bacon' IN DISTINCT(Name) THEN name NOT LIKE 'Kevin Bacon' END but could not find a method to insert it into the current code without syntax issues.

I've been on this for days now and little frustrated as to how removing an item from a list you want to devise can be so difficult or how little info can be found about this with my resulting google searches. Thanks in advance.

Answer 1

This is a shorter code that I wrote based on your code snippets. Have a look at it.

 SELECT DISTINCT(name) FROM people WHERE name IS NOT 'Kevin Bacon' and id IN
(SELECT person_id FROM stars Where movie_id IN
(SELECT movie_id FROM stars Where person_id IN
(SELECT id FROM people WHERE name IS 'Kevin Bacon' and birth = 1958)))
ORDER BY name

Answer 2

Have you tried this. First do a select to get the ID or IDs of the one and only Kevin Bacon, then do the main select while excluding the very short list of IDs that are KB.

  SELECT DISTINCT(name) FROM people WHERE id NOT IN ... AND id IN...

Answer 3

I actually figured it out by using INTERSECT. I basically copied the entire code again like this:

 SELECT DISTINCT(name) FROM people WHERE id IN
(SELECT person_id FROM stars Where movie_id IN
(SELECT movie_id FROM stars Where person_id IN
(SELECT id FROM people WHERE name = 'Kevin Bacon' and
(SELECT id FROM people WHERE birth = 1958))))
 INTERSECT
 SELECT DISTINCT(name) FROM people WHERE id and name IS NOT 'Kevin Bacon' IN
(SELECT person_id FROM stars Where movie_id IN
(SELECT movie_id FROM stars Where person_id IN
(SELECT id FROM people WHERE name IS NOT 'Kevin Bacon')))
 ORDER BY NAME

I was not able to do the COUNT(name) to determine the answer 176; but, I counted them out and the only name removed was 'Kevin Bacon'

That said, there has to be an easier way to code this SQL than basically re-pasting the code with INTERSECT and making the name IS NOT 'Kevin Bacon' change. Hopefully check50 doesn't spit back as incorrect.