Databases Querying Relational Databases Subqueries Subqueries

Ikechukwu Arum
Ikechukwu Arum
3,495 Points

related to the subquery challenge using the "IN" Keyword

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 ModelName FROM MODEL WHERE ModelID IN (SELECT ModelID FROM CAR WHERE stickerPrice >30000);

I was able to solve this problem using the code above. however I am confused as to why I am not able subquery the "Model" table instead. Below are the codes I've tried:

The code below give me errors because of "modelname"

SELECT  ModelName, ModelID, stickerPrice FROM car WHERE stickerPrice >30000 AND ModelID IN(SELECT ModelID From Model);

The code below gives me error because of the alias.

SELECT  M.ModelName, ModelID, stickerPrice FROM car WHERE stickerPrice >30000 AND ModelID IN(SELECT ModelID From Model) AS M;

Can anyone please explain if it can be done and how?

1 Answer

Steven Parker
Steven Parker
177,495 Points

The second example has two issues:

  • SELECT ModelName ... FROM car :point_left: the "car" database has no "ModelName" field
  • ... ModelID IN (SELECT ModelID from Model) :point_left: will always be true, so it won't filter anything

And for the last example, there's also two issues:

  • for "M" to be a data source for the SELECT, it must appear in a FROM or JOIN clause
  • you can't give an alias to a subquery in a WHERE clause
Ikechukwu Arum
Ikechukwu Arum
3,495 Points

thanks, i was getting a subqery with an"IN" statement confused with a subquery using a "join" statement