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

Gordon Linoff · Accepted Answer · 2016-11-05T23:01:54.257

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.

edited Nov 05 '16 at 23:01

answered Nov 05 '16 at 13:18

Gordon Linoff

1,242,037
58
646
786

Thank you, Gordon. This worked perfectly after adding the table alias before the TYPE. It is probably simple to you, but you have seriously saved me a lot of time and headache. Much appreciated. Have a great day! – steveW Nov 05 '16 at 14:17

score 0 · Answer 2 · answered Nov 05 '16 at 14:14

0

 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

answered Nov 05 '16 at 14:14

Esperento57

16,521
3
39
45

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

2 Answers2