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

Question

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 1

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 cc2.TYPE = '2D' LEFT JOIN
     CarCost cc4
     ON cd.Id = cc4.id AND cc4.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.

Answer 2

 SELECT cd.ID ,
 sum(case when cc.type='2D' then cc.cost else 0 end) 2D_COST,  
 sum(case when cc.type='4D' then cc.cost else 0 end) 4D_COST
 FROM CarDesc cd LEFT JOIN CarCost cc ON cd.Id = cc.id 
 WHERE cd.ID IN (1, 2, 3)
 group by cd.ID