Joining two tables with many to many relationship in sql

debugcn Published at Dev

k.chinni66

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

Edit 1: I have one issue after trying MT0 query. I am very much satisfied with his answer but still need some changes to be done:

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|1.0
1|p1|1.2

Now I want the result as

1|p1|1.0|1.0
1|p1|1.1|NULL
1|p1|1.2|1.2

But I am getting as

1|p1|1.0|1.0
1|p1|1.1|1.2
1|p1|1.2|NULL

Please do some help on this

MT0

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

Collected from the Internet

Please contact [email protected] to delete if infringement.