PSET 7 Movies SQL Q9

Question

In 9.sql, write a SQL query to list the names of all people who starred in a movie released in 2004, ordered by birth year.

...Your query should output a table with a single column for the name of each person.

...People with the same birth year may be listed in any order.

...No need to worry about people who have no birth year listed, so long as those who do have a birth year are listed in order.

...If a person appeared in more than one movie in 2004, they should only appear in your results once.

I don't understand why my code only outputs one name instead of the 17k+ it is supposed to. Any/all help with understanding this problem is appreciated!

My code is below:

SELECT DISTINCT name FROM people WHERE id = 
(SELECT person_id FROM stars WHERE movie_id = (SELECT id FROM movies WHERE year = 2004))
ORDER BY birth;

Answer 1

The issue here was in the syntax of the SQL query. When looking for multiple elements from a subquery, it's necessary to use "IN" in the query. In other words look for all elements A that are IN search B.

Simply put, the query incorrectly used "=" in two places where it should have used "IN".

In this case (as was sorted out in the comments before this answer) the query should have been written like this:

SELECT DISTINCT name FROM people WHERE id IN 
(SELECT person_id FROM stars WHERE movie_id IN 
(SELECT id FROM movies WHERE year = 2004))

This is a query of a subquery of a subquery involving 3 different tables.

Glad you got it working! ;-)

If this answers your question, please click on the check mark to accept. Let's keep up on forum maintenance. ;-)

ORDER BY birth;