steveW steveW - 29 days ago 10
SQL Question

SQL select same column with different where, then join and append to outer query

I am new to SQL. I have two tables, CarDesc and CarCost. The CarDesc table drives the query population. I am trying to select the CAR_DESC column where ID In List(1,2,3) and then join on ID to the CarCost table to return a column for the 2D cost and a column for the 4D cost.

CarDesc

ID CAR_DESC COUNTRY
1 Toyota Japan
2 Honda Japan
3 Kia Korea
4 Jeep USA
5 Ford USA


CarCost

ID TYPE COST
1 2D 3000
1 4D 2700
2 2D 4000
2 4D 5500
3 2D 3200
3 4D 5000


This is the expected result I am trying to receive: One distinct record for each car description and then a column for the 2D cost and then a column for the 4D cost

CAR_DESC 2D_COST 4D_COST
Toyota 3000 2700
Honda 4000 5500
Kia 3200 5000


I think I am close with the SQL, but I just keep getting different error messages as I tweak the code. Can you guys please help me with my SQL? This is what I have so far...

SELECT DESC
FROM CarDesc
WHERE ID IN LIST (1,2,3)
LEFT JOIN (SELECT COST AS 2D_COST
WHERE TYPE = 2D)
ON CarDesc.ID = CarCost.ID
LEFT JOIN (SELECT COST AS 4D_COST
WHERE TYPE = 4D)
ON CarDesc.ID = CarCost.ID

Answer

The query will would look something like this:

SELECT cd.*, cc2.cost as cost_2d, cc4.cost as cost_4d
FROM CarDesc cd LEFT JOIN
     CarCost cc2
     ON cd.Id = cc2.id AND TYPE = '2D' LEFT JOIN
     CarCost cc4
     ON cd.Id = cc4.id AND TYPE = '4D'
WHERE cd.ID IN (1, 2, 3);

You have numerous errors in your query. It is not worth trying to explain them. Examine this query to understand how it should work.

Comments