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

Kerry Collier
seal-mask
.a{fill-rule:evenodd;}techdegree
Kerry Collier
Front End Web Development Techdegree Student 16,151 Points
Posted by Kerry Collier
.a{fill-rule:evenodd;}techdegree
Kerry Collier
Front End Web Development Techdegree Student 16,151 Points

Querying Relational Databases Subqueries Challenge 1 of 4

So, I'm returning all the right info from what I can tell, but I'm still getting an error saying the Subquery isn't returning the ModelID. I'm not sure what I have wrong. I've tried everything I can think of..anyone have any thoughts?

Challenge: 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

My attempt: Bummer! The subquery doesn't return all ModelIDs for cars with the sticker price greater than 30000.

SELECT Model.ModelID, ModelName, StickerPrice FROM Model LEFT OUTER JOIN Car ON Model.ModelID = Car.ModelID WHERE Car.CarID IN(SELECT Car.CarID FROM Car WHERE StickerPrice > 30000);

ModelID ModelName StickerPrice 4 Impala 32000.0 6 Colorado 34000.0 7 Cherokee 35500.0 7 Cherokee 37900.0 7 Cherokee 36900.0 8 Grand Cherokee 39900.0 8 Grand Cherokee 42500.0 11 Accord 32500.0 11 Accord 38500.0 15 Camry 32000.0 16 Sienna 41000.0 16 Sienna 39500.0

4 Answers

Steven Parker
Steven Parker
231,248 Points
Steven Parker
Steven Parker
231,248 Points

:point_right: The challenge is to use a subquery instead of a join.

Technically, this task can be done with either a subquery or a join, but you managed to use both. While you do get the correct results, the query is unnecessarily complex.

Simplify your query to use a subquery only.. The "bummer" message already hinted that they expect the subquery to return ModelID's. So starting from "FROM", write something like "FROM Model WHERE ModelID IN ..." and create your subquery from there.

Gavin Mace
Gavin Mace
30,747 Points

Can you give me any tips further for where I'm going wrong here? .... The question only asks for Model Names, but then the ERROR says "Not showing ModelID" ... which is kinda contradictory.

Q) 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

SELECT Model.ModelName
FROM Model 
WHERE ModelID
IN(
SELECT Car.CarID 
FROM Car 
WHERE StickerPrice > 30000);

I'm basically down to just trying different combinations, so any help = muchas gracias :)

EDIT - Haha CRAP! ... Just changed CardID to ModelID .... man that took so long to figure out.

Juan Emmanuel Afable
Juan Emmanuel Afable
18,531 Points
Juan Emmanuel Afable
Juan Emmanuel Afable
18,531 Points 
SELECT ModelName FROM Model WHERE ModelID IN (SELECT ModelID FROM Car WHERE StickerPrice > 30000);

Just like where we join the two tables using INNER JOIN with the ModelID. You would want the inner query to return the ModelIDs with StickerPrices > 30,000. Then you match those with the ModelNames.

Kevin Gates
Kevin Gates
15,053 Points
Kevin Gates
Kevin Gates
15,053 Points

In 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.

Lewis Cowles
Lewis Cowles
74,902 Points
Lewis Cowles
Lewis Cowles
74,902 Points

As Steven Parker says use IN instead of JOIN, but I'm more interested in Challenge 4 of 4...

I know my results are correct because I downloaded the data with SELECT * FROM Sale and SELECT * FROM Customer

I've tried a lot!

Using Customer as the FROM with Sale as INNER JOIN on subquery, Using Sale as FROM with Customer as INNER JOIN on subquery, even a subquery in the subquery, which made me thing of a meme as this shouldn't have a subquery, just a JOIN. It's very annoying and I cannot help but think the challenge is bugged

SELECT S.* FROM Customer C INNER JOIN(SELECT * FROM Sale) S ON S.CustomerID = C.CustomerID WHERE Gender = 'F';

SaleID  CarID   CustomerID  LocationID  SalesRepID  SaleAmount  SaleDate
2   2   2   1   1   25000.0     2015-06-01
3   1   3   1   2   25000.0     2015-06-01
7   9   7   1   3   16250.0     2015-07-05
8   8   9   2   5   30999.0     2015-07-05
11  12  11  1   1   33750.0     2015-08-06
15  17  14  2   6   27300.0     2015-08-29
16  16  16  2   6   42250.0     2015-09-02
20  20  21  1   1   21500.0     2015-10-10
23  24  23  1   1   27800.0     2015-10-20
25  22  24  1   3   27400.0     2015-11-01
26  29  26  1   2   32000.0     2015-11-05

SELECT S.* FROM Sale S INNER JOIN(SELECT * FROM Customer WHERE Gender = 'F') C ON S.CustomerID = C.CustomerID;

SaleID  CarID   CustomerID  LocationID  SalesRepID  SaleAmount  SaleDate
2   2   2   1   1   25000.0     2015-06-01
3   1   3   1   2   25000.0     2015-06-01
7   9   7   1   3   16250.0     2015-07-05
8   8   9   2   5   30999.0     2015-07-05
11  12  11  1   1   33750.0     2015-08-06
15  17  14  2   6   27300.0     2015-08-29
16  16  16  2   6   42250.0     2015-09-02
20  20  21  1   1   21500.0     2015-10-10
23  24  23  1   1   27800.0     2015-10-20
25  22  24  1   3   27400.0     2015-11-01
26  29  26  1   2   32000.0     2015-11-05

SELECT S.* FROM Sale S INNER JOIN(SELECT CustomerID FROM Customer WHERE Gender = 'F') C ON S.CustomerID = C.CustomerID;

2   2   2   1   1   25000.0     2015-06-01
3   1   3   1   2   25000.0     2015-06-01
7   9   7   1   3   16250.0     2015-07-05
8   8   9   2   5   30999.0     2015-07-05
11  12  11  1   1   33750.0     2015-08-06
15  17  14  2   6   27300.0     2015-08-29
16  16  16  2   6   42250.0     2015-09-02
20  20  21  1   1   21500.0     2015-10-10
23  24  23  1   1   27800.0     2015-10-20
25  22  24  1   3   27400.0     2015-11-01
26  29  26  1   2   32000.0     2015-11-05

Okay, maybe you want Gender as a where clause?

SELECT S.* FROM Sale S INNER JOIN(SELECT CustomerID, Gender FROM Customer) C ON S.CustomerID = C.CustomerID  WHERE C.Gender = 'F';

SaleID  CarID   CustomerID  LocationID  SalesRepID  SaleAmount  SaleDate
2   2   2   1   1   25000.0     2015-06-01
3   1   3   1   2   25000.0     2015-06-01
7   9   7   1   3   16250.0     2015-07-05
8   8   9   2   5   30999.0     2015-07-05
11  12  11  1   1   33750.0     2015-08-06
15  17  14  2   6   27300.0     2015-08-29
16  16  16  2   6   42250.0     2015-09-02
20  20  21  1   1   21500.0     2015-10-10
23  24  23  1   1   27800.0     2015-10-20
25  22  24  1   3   27400.0     2015-11-01
26  29  26  1   2   32000.0     2015-11-05

I Even tries Sub-querying the sub-query...

SELECT S.* FROM Customer C INNER JOIN(SELECT * FROM Sale WHERE CustomerID IN(SELECT CustomerID FROM Customer WHERE Gender = 'F')) S ON S.CustomerID = C.CustomerID

SaleID  CarID   CustomerID  LocationID  SalesRepID  SaleAmount  SaleDate
2   2   2   1   1   25000.0     2015-06-01
3   1   3   1   2   25000.0     2015-06-01
7   9   7   1   3   16250.0     2015-07-05
8   8   9   2   5   30999.0     2015-07-05
11  12  11  1   1   33750.0     2015-08-06
15  17  14  2   6   27300.0     2015-08-29
16  16  16  2   6   42250.0     2015-09-02
20  20  21  1   1   21500.0     2015-10-10
23  24  23  1   1   27800.0     2015-10-20
25  22  24  1   3   27400.0     2015-11-01
26  29  26  1   2   32000.0     2015-11-05

SELECT SaleID, CarID, CustomerID, LocationID, SalesRepID, SaleAmount, SaleDate FROM (SELECT S.*, C.Gender FROM Sale S INNER JOIN Customer C ON S.CustomerID = C.CustomerID) O WHERE Gender = 'F'

SaleID  CarID   CustomerID  LocationID  SalesRepID  SaleAmount  SaleDate
2   2   2   1   1   25000.0     2015-06-01
3   1   3   1   2   25000.0     2015-06-01
7   9   7   1   3   16250.0     2015-07-05
8   8   9   2   5   30999.0     2015-07-05
11  12  11  1   1   33750.0     2015-08-06
15  17  14  2   6   27300.0     2015-08-29
16  16  16  2   6   42250.0     2015-09-02
20  20  21  1   1   21500.0     2015-10-10
23  24  23  1   1   27800.0     2015-10-20
25  22  24  1   3   27400.0     2015-11-01
26  29  26  1   2   32000.0     2015-11-05

Id appreciate either knowing why my code is broken, or what I'm missing as it is super annoying!

Kevin Gates
Kevin Gates
15,053 Points
Kevin Gates
Kevin Gates
15,053 Points

Challenge 4 should have its own Forum question.