Joining two tables with many to many relationship in sql

Question

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

Answer 1

Please try below.

create table tab1(Col1 int,col2 varchar(10), col3 varchar(10))

insert into tab1
values(1,'p1','1.0'),
(1,'p1','1.1'),
(1,'p1','1.2')

create table tab2(Col1 int,col2 varchar(10), col3 varchar(10))

insert into tab2
values(1,'p1','2.0'),
(1,'p1','2.1'),
(1,'p1','2.2')

SELECT a.col1,a.col2,a.col3,b.col3
FROM
(
    select 
    *,ROW_NUMBER() over(order by col1) as rownum
    from tab1
)a
inner join 
(
    select *,ROW_NUMBER() over(order by col1) as rownum
    from tab2
)b ON a.rownum = b.rownum and a.Col1 = b.Col1

Answer 2

Create one more column only having the matching ID in both tables like

alter table 1
add column1 char(100)
alter table 2
add column2 char(100)
so table 1
Column1 | column 2| column 3|column4
1|p1|1.0                     
1|p1|1.1
1|p1|1.2
update table table1
set column4 = left(column1,4)

Do the same in table 2 and use that as a key for left join.

Answer 3

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    

Answer 4

You could add a calculated column with the numbering, using the row_number window function and use that in the join:

SELECT t1.column1, t2.column2, t1.column3, t2.column3
FROM   (SELECT column1, column2, column3,
               ROW_NUMBER() OVER (PARTITION BY column1, column2
                                  ORDER BY     column3) AS rn
        FROM   table1) t1
JOIN   (SELECT column1, column2, column3,
               ROW_NUMBER() OVER (PARTITION BY column1, column2
                                  ORDER BY     column3) AS rn
        FROM   table2) t2 ON t1.column1 = t2.column1 AND
                             t1.column2 = t2.column2 AND
                             t1.rn      = t2.rn