k.chinni66 k.chinni66 - 6 months ago 12
SQL Question

Joining two tables with many to many relationship in sql

I have two tables with many to many relationship. I need to join them and get the matched records.

Table 1

Column1 | column 2| column 3|
1|p1|1.0
1|p1|1.1
1|p1|1.2

Table 2

Column1 | column 2| column 3|
1|p1|2.0
1|p1|2.1
1|p1|2.2


Now I want the result as

1|p1|1.0|2.0
1|p1|1.1|2.1
1|p1|1.2|2.2


I mean column1 and column2 matching and showing values from both columns for column3

MT0 MT0
Answer

If you have unequal numbers of rows for each partition then you can do:

Oracle Setup:

CREATE TABLE table1 ( col1, col2, col3 ) AS
SELECT 1, 'P1', '1.0' FROM DUAL UNION ALL
SELECT 1, 'P1', '1.1' FROM DUAL UNION ALL
SELECT 1, 'P1', '1.2' FROM DUAL UNION ALL
SELECT 1, 'P2', '1.0' FROM DUAL UNION ALL
SELECT 1, 'P2', '1.2' FROM DUAL UNION ALL
SELECT 2, 'P1', '1.0' FROM DUAL;

CREATE TABLE table2 ( col1, col2, col3 ) AS
SELECT 1, 'P1', '2.0' FROM DUAL UNION ALL
SELECT 1, 'P1', '2.1' FROM DUAL UNION ALL
SELECT 1, 'P1', '2.2' FROM DUAL UNION ALL
SELECT 1, 'P2', '2.1' FROM DUAL UNION ALL
SELECT 2, 'P1', '2.0' FROM DUAL UNION ALL
SELECT 2, 'P1', '2.1' FROM DUAL;

Query:

SELECT COALESCE( t1.col1, t2.col1 ) AS col1,
       COALESCE( t1.col2, t2.col2 ) AS col2,
       t1.col3 AS t1col3,
       t2.col3 AS t2col3
FROM   (
         SELECT t.*,
                ROW_NUMBER() OVER ( PARTITION BY col1, col2
                                    ORDER BY col3 ) AS rn
         FROM   table1 t
       ) t1
       FULL OUTER JOIN
       (
         SELECT t.*,
                ROW_NUMBER() OVER ( PARTITION BY col1, col2
                                    ORDER BY col3 ) AS rn
         FROM   table2 t
       ) t2
       ON ( t1.col1 = t2.col1 AND t1.col2 = t2.col2 AND t1.RN = t2.rn )
ORDER BY col1, col2, t1col3 NULLS LAST, t2col3 NULLS LAST;

Output:

      COL1 COL2 T1COL3 T2COL3
---------- ---- ------ ------
         1 P1   1.0    2.0    
         1 P1   1.1    2.1    
         1 P1   1.2    2.2    
         1 P2   1.0    2.1    
         1 P2   1.2           
         2 P1   1.0    2.0    
         2 P1          2.1