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
Bernt Erik Engebretsen
5,377 Points"bummer!" as answer when gives correct result in sql playground
Challenge Task 1 of 4
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 subquery along with IN to list all the Model Names with a Sticker Price greater than $30000 Type in your command below. Bummer! The subquery doesn't return all ModelIDs for cars with the sticker price greater than 30000.
My query:
SELECT m.ModelName, c.StickerPrice
FROM Model m INNER JOIN Car c ON m.ModelID = c.ModelID
WHERE c.StickerPrice IN (
SELECT StickerPrice
FROM Car
WHERE StickerPrice > 30000
);
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,175 PointsThat is link to challenge
https://teamtreehouse.com/library/querying-relational-databases/subqueries/subqueries
Can't pass it as well...
The error is explainable, but I've no idea how to solve it...
Program basically checks that subquery with IN should return ModelId.
Your subquery SELECT StickerPrice FROM Car WHERE StickerPrice > 30000 obviously return StickerPrice and not
ModelId.
that is why error occurs, I've no idea how to solve problem otherwise:
i tried:
SELECT m.ModelName, c.StickerPrice
FROM Model m INNER JOIN Car c ON m.ModelID = c.ModelID
WHERE c.ModelId IN (
SELECT ModelId
FROM Car
WHERE StickerPrice > 30000
);
Does not work :(
The other constraint in challenge that you cannot write anyhing in JOIN, like JOIN ( ... ) will fail.
so we cannot write MORE than ONE subqueries. It has to be one subquery and with IN keyword.
It also MUST return ModelId and ONLY ModelId.
How to do it - no idea ...
4 Answers
Alexander Nikiforov
Java Web Development Techdegree Graduate 22,175 PointsCheck out solution here. The guy here explained how it should look like:
https://teamtreehouse.com/community/help-with-challenege-1-of-4
Prince Vonleh
Courses Plus Student 4,552 PointsSELECT ModelName FROM Model WHERE ModelID IN ( SELECT ModelID FROM Car WHERE StickerPrice > 30000 );
Kevin Gates
15,053 PointsIn this instance is isn't required to give the sticker price, but only to give the Model Name. Therefore, we don't need to alias the table names.
SELECT ModelName
FROM Model
WHERE ModelID IN
(SELECT ModelID FROM Car WHERE StickerPrice > 30000);
The subquery gives me the Model Ids of cars where the sticker price is greater than 30,000. The resulting dataset is utilized in our main query to show only the Model Names in our Model Table by having our WHERE clause compare All Model Ids against those IDs above 30,000 sticker price.
Kawaljeet Singh
3,820 PointsWe do not need to inner join ,below is the correct answer
select modelname from model where modelid in (select modelid from car where stickerprice>30000);
Jason Anders
Treehouse Moderator 145,860 PointsJason Anders
Treehouse Moderator 145,860 PointsHey Bernt,