Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Michael Mason
Michael Mason
4,677 Points

Can't Progress Past Challenge Task 1 of 4 - Unsure of what fields the result dataset should contain...

In the Challenge description of Challenge Task 1, it asks to return all Model Names (Model.ModelName) for cars for Sticker Prices (Car.StickerPrice) over $30,000. Using the knowledge I have about the JOIN and WHERE <field> IN (<subquery>) clauses, I wrote the following SQL Statement: SELECT Model.ModelName FROM Model OUTER LEFT JOIN Car ON Model.ModelID = Car.ModelID WHERE Car.StickerPrice IN (SELECT StickerPrice FROM Car WHERE StickerPrice > 30000);

Not only does this return duplicative models in the result set, I get the error "The subquery doesn't return all ModelIDs for cars with the sticker price greater than 30000." The error statement suggests that I should return ModelIDs in lieu of Model Names, so I revised my query to state: SELECT Model.ModelID FROM Model OUTER LEFT JOIN Car ON Model.ModelID = Car.ModelID WHERE Car.StickerPrice IN (SELECT StickerPrice FROM Car WHERE StickerPrice > 30000);

After submitting this answer, I got the same error statement. At this point, I am not sure what Challenge Task is looking for. There does not appear to be any fault in my WHERE <field1> IN (SELECT <field1> ...) criteria. Adding the field "Car.StickerPrice" to the SELECT clause of my main query suggests that no results equal to or under $30,000 are returned. Also, changing my JOIN clause from "OUTER LEFT" to "INNER" does not seem to change the results, even when selecting only the ModelID or ModelName field from the Model table in an attempt to limit duplicate values.

It seems I've tried every combination of fields in my SELECT statement to try and satisfy the Challenge Task's requirements, but to no avail. Is there in error in my WHERE clause,, or is the Challenge Task looking for a specific field in my result set, or could it be that there is a glitch/error in the code that is preventing me from progressing forward?

Steven Parker
Steven Parker
231,248 Points

1 Answer

Steven Parker
Steven Parker
231,248 Points

:point_right: The correct syntax is: LEFT OUTER JOIN.

It may not like "OUTER LEFT JOIN". But more importantly, I believe the challenge wants you to use a subquery with "IN" as an alternative to using a "JOIN", not using both together.

The hint you got after your first attempt is that the subquery (not your outer query) is not returning the correct ModelIds. The suggestion being made is that the subquery should return Model Ids, which would then be used to filter the outer query results using a WHERE ... IN clause.

Michael Mason
Michael Mason
4,677 Points

You are right, Steven. The correct syntax should be LEFT OUTER JOIN, not OUTER LEFT. On that note, I tried deleting my join altogether and used the Model table in my main query to SELECT ModelName but used Model.ModelID in my WHERE clause to search IN the results from the subquery, which selects all ModelIDs from the Car table WHERE StickerPrice > 30000. That did the trick! Thanks!