Subquery using the "IN" Keyword

Question

The problem: 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

I was able to solve the problem using:

SELECT ModelName FROM MODEL 
WHERE ModelID IN (SELECT ModelID FROM CAR WHERE stickerPrice >30000);

however, I am trying to subquery the "MODEL" table instead to display the "model name" and "sticker price" using the "IN" keyword but I am getting some errors. I tried:

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

but I got and error because "modelname" is a field on the Model table. I also tried alias:

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

but I get a syntax error because of the "AS" keyword.

I just not sure why doing it the other way will not work as expected.

score 1 · Answer 1 · edited Apr 04 '19 at 19:19

1

Your first query is the way to go (using the "IN" / sub-query approach).

In the second attempt you're selecting data from the "car" table which doesn't have a column "ModelName". It's just doesn't work this way. Don't really get the idea behind your second approach.

An alternative solution would be joining the data together somehow like this:

SELECT MODEL.ModelName
FROM MODEL
JOIN CAR
  ON MODEL.ModelID = CAR.ModelID
WHERE CAR.StickerPrice > 30000;

edited Apr 04 '19 at 19:19

Marcus Adams

53,009
9
91
143

answered Apr 04 '19 at 19:09

Flo

189
2
7

OH I see, I guess was getting the subquery and a join statement mixed up – user8964654 Apr 04 '19 at 19:15
You could have duplicate model names with this method. – Marcus Adams Apr 04 '19 at 19:23

Subquery using the "IN" Keyword

1 Answers1