0

I currently working at CS50 problem set 7, called Movies. This is about SQL, writing query to complete each tasks. There's one task that i need to list the names of all people who starred in a movie in which Kevin Bacon also starred.

This is my code :

-- show every actor starred in a movie that Kevin Bacon also starred in WITHOUT appearing Kevin Bacon itself --

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

I almost succeded. . This output 177 rows. Which is 177 actors. I have already DISTINCT each of them. BUT the result should be 176 rows. Then I realized, as the assignment wanted me to, I shouldn't include Kevin Bacon himself on the list.

Please help me. This my first time playing with SQL. How to remove Kevin Bacon itself from the list? What should i add to my query code?

Shadow
  • 33,525
  • 10
  • 51
  • 64

1 Answers1

0

You must exclude the id fro persons, spo you must also get the id From Kevon Bacon and exclude it

SELECT DISTINCT name FROM
movies JOIN stars ON movies.id = stars.movie_id JOIN
people ON stars.person_id = people.id
WHERE movies.id IN (SELECT movie_id FROM stars WHERE person_id IN(SELECT id FROM people WHERE name = "Kevin Bacon" AND birth = 1958))
AND  people.id NOT IN (SELECT id FROM people WHERE name = "Kevin Bacon" AND birth = 1958);
nbk
  • 45,398
  • 8
  • 30
  • 47