Outer Join on all columns if possible, or a subset of columns if not possible, and sum

Question

I have two tables, call them T1 and T2 each with 4 columns A, B, C, Quantity.

I want to do an outer join. However, some ABC combinations in T1 do not match with T2, and vice versa.

My goal is to join with the following logic:

I want to join on all columns ABC if possible and return T1.A,T1.B,T1.C,T2.Quantity.

If there is no match at A,B,C then for a given T1.A, T1.B, join with T2.A, T2.B, sum(T2.Quantity). That is, I group by all T2.A, T2.B that match the T1.A, T1.B and get the sum of all T2.Quantity in that group.

I know this can be done by grouping T2 in subqueries and then left joining on T1, but could this be done with window functions?

Example:

Let T1:

A = 1
B = 2
C = 3
Quantity = 10

T2 Row1:

A = 1
B = 2
C = 4
Quantity = 20

T2 Row2:

A = 1
B = 2
C = 5
Quantity = 30

I want to return:

T1.A = 1
T1.B = 2
T1.C = 3
SUM(T2.Quantity) = 50 (sum of quantity in both rows in T2 that match T1.A,T1.B since T1.C do not match)

• Andriy M: What can we assume about the data? Can, for instance, T1 have both an (A,B,C) combination that matches a row in T2, and another (A,B,C) combination (with the same A and B but a different C) that doesn't? What would the output be in that case? There might be other possibilities as well that need some elaboration.

Answer 1

You will need three left joins. Semipseudo code as stuck in trafic

Select sum(coalesce(l1,l2,l3 quantity)
From t1
Left Join t2 as l1 on a,b,c
Left join t2 as l2 on a,b and l1 is null
Left join t2 as l3 on a and l1 is null and l2 is null