SQL join on multiple columns using summated by sum on the right table

advertisements

I have two tables (Table A and Table B) which I want to join on multiple columns. But the third criteria for the join would be a sum of values on table B.

Table A:

Col1     Col2     Col3
=======================
AX1      AY1       1
AX1      AY2       7
AX1      AY3       9
AX2      AY1       1

Table B:

Col1     Col2     Col3
=======================
BX1      BY1       0,5
BX1      BY1       0,5
BX1      BY2       3
BX1      BY2       3
BX2      BY1       1

So the purpose would be to join on

    A.col1 = B.Col1
    AND A.Col2 = B.Col2
    AND A.Col3 = sum(B.Col3)
GROUP BY
   B.Col1, B.Col2

And to make things easier, the final purpose would be to exclude the match between table A & B. So final results would be the records from table A where the sum doesn't match in table B, and displaying values from both tables. (in other words, set A join set B exluding the common set)

Table Result:

Col1     Col2     A.Col3   B.Col3
=================================
X1       Y2        7        6
X1       Y3        9        null

I have tried multiple full outer joins, taking only the null keys, but never managed to get the right result. I usually only get the X1-Y2 value from table but not the X1-Y3.

Thanks!

Nikbe


Try this:

SELECT A.*, B.col3_sum
 FROM A
 LEFT JOIN (
  SELECT Col1, Col2, SUM(Col3) col3_sum
   FROM B
   GROUP BY Col1, Col2
 ) B ON A.Col1 = B.Col1
    AND A.Col2 = B.Col2
 WHERE B.col3_sum IS NULL
    OR B.col3_sum <> A.Col3

Test it: http://sqlfiddle.com/#!2/23fb77/3