CODEWITHSUNDEEP
sqljoin
NikBe
NikBe

Reputation: 21

SQL join on multiple columns using grouped by sum on right table

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

Upvotes: 0

Views: 137

Answers (3)

NikBe
NikBe

Reputation: 21

Thanks @Rimas & @DirkNM, both solutions return the right result!

Rimas' solution is quicker at execution time though (and easier to read)

COL1 COL2           COL3   COL3_SUM
---- -------- ---------- ----------
X1   20140202          7          6 
X1   20140303          9            


NVL(A.COL1,B.COL1) NVL(A.COL2,B.COL2)     A_COL3     B_COL3
------------------ ------------------ ---------- ----------
X1                 20140303                    9            
X1                 20140202                    7          6

Upvotes: 0

Rimas
Rimas

Reputation: 6024

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

Upvotes: 1

DirkNM
DirkNM

Reputation: 2664

Try this (not really tested), maybe there is a simpler solution:

SELECT NVL(a.col1, b.col1),
       NVL(a.col2, b.col2),
       MAX(a.col3) AS a_col3,
       MAX(b.col3) AS b_col3
  FROM ta a
  FULL
 OUTER 
  JOIN(SELECT col1,
              col2,
              SUM(col3) AS col3
         FROM tb
        GROUP
           BY col1,
              col2
      ) b
    ON a.col1 = b.col1
   AND a.col2 = b.col2
   AND a.col3 = b.col3
 WHERE b.col3 IS NULL OR a.col3 IS NULL 
 GROUP
    BY NVL(a.col1, b.col1),
       NVL(a.col2, b.col2)

Upvotes: 0

Related Questions