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

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)

• 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. May 4, 2021 at 21:36

``````Select sum(coalesce(l1,l2,l3 quantity)