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)

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