>Tab1 >Key1 Value1 >1 1000 >2 2000 >3 3000 >4 4000 > >Tab2 >Key2 Key1 Value2 >1 1 400 >2 1 400 >3 2 2000 >4 3 1000 >5 3 1000 >6 3 500 > >Result >Key1 Key2 Value >1 1 400 >1 2 400 >1 null 200 >2 3 2000 >3 4 1000 >3 5 1000 >3 6 500 >3 null 500 >4 null 4000 > >Note: >In Tab2 are 0 to n related records for each record in Tab1. >In return dataset should be one record with key2 is null if value1 <> >sum(value2) for one relation.
Assuming Key1 and Key2 are primary keys, this should be quite simple: WITH TabSum (Key1, Value1, Value2) as (SELECT t1.Key1, t1.Value1, sum(t2.Value2) FROM Tab1 t1 LEFT JOIN Tab2 t2 ON t1.Key1 = t2.Key1 GROUP BY 1, 2) SELECT Key1, Key2, Value2 FROM Tab2 UNION SELECT Key1, null, Value1 - coalesce(Value2, 0) FROM TabSum WHERE Value1 is distinct from Value2 I'm uncertain whether you need to add ORDER BY 1, 2 NULLS LAST to this statement or not. HTH, Set
