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 Querying Relational Databases Subqueries Subqueries

Posted by Sam Beattie
Sam Beattie
2,413 Points

Not sure what I'm getting wrong here. It says I'm missing the "IN keyword," but where would I put it?

https://teamtreehouse.com/library/querying-relational-databases/subqueries/subqueries

KRIS NIKOLAISEN
KRIS NIKOLAISEN
54,971 Points

Which task are you referring to?

5 Answers

Yassin Chiguer
Yassin Chiguer
5,228 Points
Yassin Chiguer
Yassin Chiguer
5,228 Points

Try this: select modelname from model where modelid in (select modelid from car where stickerprice > 30000)

ivana kantnerova
ivana kantnerova
15,932 Points
ivana kantnerova
ivana kantnerova
15,932 Points

Select ModelName from Model where ModelID IN ( Select ModelID from Car where StickerPrice > 30000)

Impala Colorado Cherokee Grand Cherokee Accord Camry Sienna

Gia Hoang Nguyen
Gia Hoang Nguyen
4,137 Points
Gia Hoang Nguyen
Gia Hoang Nguyen
4,137 Points

Select ModelName from Model where ModelID IN ( Select ModelID from Car where StickerPrice > 30000)

Can someone give me a clear explanation for this please?

Thank you!

ivana kantnerova
ivana kantnerova
15,932 Points
ivana kantnerova
ivana kantnerova
15,932 Points

the first one will be made a "nested select", and the result of this selection will be a group of ids the second one will be the "main select" which will show the names of cars, which ids are in that group

Gia Hoang Nguyen
Gia Hoang Nguyen
4,137 Points

Technically, does it mean that because 2 tables share the same ModelID so I can use the Where clause with

Where ModelID IN ( Select ModelID from Car where StickerPrice > 30000)?

My other approach for the challenge is to use the Left Join to combine 2 tables without using IN like below

Select M.ModelID, M.ModelName from Model M Left Join Select C.ModelID, C.StickerPrice from C On M.ModelID = M.ModelID where Sticker Price > 30000;

Is this also correct?

Which approach will be more optimal?

Thank you! Gia

ivana kantnerova
ivana kantnerova
15,932 Points
ivana kantnerova
ivana kantnerova
15,932 Points

technically, it means that when two (or more) tabels have the ids which have the same value (as a primary key in the one table and foreign key on the other table(s)) you can compare them and use restrictions .. for ex. where .. and doesn't matter if it is in nested select or you use directly join between them

more optimal .. it depends of how many records have the table car in nested select... if we suppose a lot, then the better solution will be without nested select because .. in the first solution .. you first have to go through every records in table car (and take a group of id) and then you again have to go through every records in table model and compare ... with join you do it just ones, because first you "join" the records from both tables into one set straight with restriction

but if the table car has only a few records ... nested select will be quick and then it doesn't matter of it

i hope it'll help ... :)) ivana