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
MANOJ MANN
1,793 PointsHow to use a sub query along with IN to list all the Model Names with a Sticker Price greater than $30000
In a car database there is a Model table with columns, ModelID, MakeID and ModelName and a Car table with columns, CarID, ModelID, VIN, ModelYear and StickerPrice.
Use a sub query along with IN to list all the Model Names with a Sticker Price greater than $30000 My code is: Select M.ModelName from A Where Stickerprice in ( Select * Model M Inner Join Car C on M.ModelID = C.ModelID where Stickerprice < $30000) as A
4 Answers
MANOJ MANN
1,793 PointsThanks. My new code is working now:
Select ModelName From Model Where ModelID IN (Select ModelID From Car Where stickerprice > '30000')
Steven Parker
231,236 PointsI don't think you can give a sub-query in the WHERE clause an alias and then refer to it as the source ("FROM") of the SELECT. But you don't need anything that fancy anyway, you can select from the "Model" table directly and just use the sub-query for filtering.
Also, you won't need to JOIN in the sub-query if you return a field that is common to both tables to compare with.
MANOJ MANN
1,793 PointsHi Steven, My new code is:
Select ModelName from Model Where modelid IN (Select Stickerprice from Car Where stickerprice > $30000)
However, it is still displaying error when I execute it. Please help.
Steven Parker
231,236 PointsYou're very close now, but you're comparing the modelid to the stickerprice in the outer WHERE clause. Try SELECTing the modelid in the sub-query.
MANOJ MANN
1,793 PointsI am finding it difficult to write the correct code. I have tried a new code which has also not worked.
Select ModelName from Model WHERE ModelName IN (Select ModelID from Car Where stickerprice > $30000)
Steven Parker
231,236 PointsChanging the sub-query to ModelID would have done the job, but you also changed the main WHERE so it now compares it to ModelName. Put the main WHERE back to compare the ModelID with the sub-query and you should have it.
MANOJ MANN
1,793 PointsI need help to a correct code to over come this challenge