db2 sql left join table help

Question

I have a sql query like this,

    select 
    t1.id as ID,
    case when t2.field1 = 1102 then (t2.field3 - t2.field2) end as A,
    case when t2.field1 = 1112 then (t2.field3 - t2.field2) end as B,
    case when t2.field1 = 1113 then (t2.field3 - t2.field2) end as C,
    case when t2.field1 = 1106 then (t2.field3 - t2.field2) end as D 
   from table1 t1
   left join table2 t2
    on t1.id = t2.id

and the result is like this;

 ID   A      B     C     D
 ---- ------ ----- ----- ------
 1773 100    NULL  NULL   NULL
 1773 NULL   120   NULL   NULL
 1773 NULL   NULL  200    NULL
 1773 NULL   NULL  NULL   60

but I want to show result like this;

     ID   A      B     C     D
     ---- ------ ----- ----- ------
     1773 100    120   200   60

how can I rewrite the query? thx for your help..

Answer 1

Just use sum() and group by id to flatten it out:

select 
t1.id as ID,
sum(case when t2.field1 = 1102 then (t2.field3 - t2.field2) end) as A,
sum(case when t2.field1 = 1112 then (t2.field3 - t2.field2) end) as B,
sum(case when t2.field1 = 1113 then (t2.field3 - t2.field2) end) as C,
sum(case when t2.field1 = 1106 then (t2.field3 - t2.field2) end) as D 
from table1 t1
left join table2 t2 on t1.id = t2.id
group by 1;

Efficient. Simple. Incidentally, max() or min() would work equally well.

This works because your data only has one occasion for each field where there's a non-null value; any aggregating function can pick that one value out from the nulls.

Answer 2

how about nested queries for each value?

select t1.id as ID,     
  (select t2.field3 - t2.field2 from table2 t2 
     where t1.id = t2.id and t2.field1 = 1102 ) as A,     
  (select t2.field3 - t2.field2 from table2 t2 
     where t1.id = t2.id and t2.field1 = 1112 ) as B,     
  (select t2.field3 - t2.field2 from table2 t2 
     where t1.id = t2.id and t2.field1 = 1113 ) as C,     
  (select t2.field3 - t2.field2 from table2 t2 
     where t1.id = t2.id and t2.field1 = 1106 ) as D,     
from table1 t1

It's far from optimal but it works