0

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;
4
  • Shouldn't that be where movie_id IN ... and not =??? Disclaimer: I haven't done or even read this pset yet. I'm just a humble database engineer. ;-) – Cliff B Aug 2 '20 at 5:17
  • @CliffB That doesn't change the result unfortunately :( – Kevin Rodriguez Aug 2 '20 at 5:22
  • and did you also change the first = to in too? – Cliff B Aug 2 '20 at 5:50
  • Aha! No I did not. Thank you so much!. It wasn't a flaw in my logic after all; it's just a syntax mistake. – Kevin Rodriguez Aug 2 '20 at 17:37
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;

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .