SQL join 2 tables where IDs match and each table's ID column is not unique-CodePudding

Let's say.. I have 2 tables like this

Table 1 https://i.stack.imgur.com/ryhtp.png

Table 2 https://i.stack.imgur.com/GGuzH.png

I want to join 2 table above like example output. Each table containing same ID with multiple times because of that simple join is increasing row number of result. Basically what I want is just merge that 2 table. Is it possible?

Example Output https://i.stack.imgur.com/TUdGZ.png

CodePudding user response：

One option might be to use row_number analytic function (to get values you'll later join tables on) and then use full outer join. Something like this:

Sample data:

SQL> with
  2  t1 (order_id, t1_col1, t1_col2) as
  3    (select 1, 15000, 'sub fee' from dual union all
  4     select 1, 10000, 'deposit' from dual union all
  5     select 2,  5000, 'sub fee' from dual union all
  6     select 2, 10000, 'deposit' from dual union all
  7     select 3,  5000, 'fee'     from dual union all
  8     select 3,  5000, 'sub fee' from dual union all
  9     select 3,  1000, 'deposit' from dual
 10    ),
 11  t2 (order_id, t2_col1, t2_col2) as
 12    (select 1,  5000, 'cash' from dual union all
 13     select 1, 10000, 'card' from dual union all
 14     select 1, 10000, 'bank' from dual union all
 15     select 2, 15000, 'cash' from dual union all
 16     select 3,  7000, 'cash' from dual union all
 17     select 3,  3500, 'card' from dual union all
 18     select 3,   500, 'bank' from dual
 19    ),
 20  --

Calculate row numbers per each order_id:

 21  temp1 as
 22    (select t1.*,
 23       row_number() over (partition by order_id order by t1_col1) rn
 24     from t1
 25    ),
 26  temp2 as
 27    (select t2.*,
 28       row_number() over (partition by order_id order by t2_col1) rn
 29     from t2
 30    )
 31  --

Finally, outer join temps:

 32  select nvl(a.order_id, b.order_id) order_id,
 33     a.t1_col1, a.t1_col2, b.t2_col1, b.t2_col2
 34  from temp1 a full outer join temp2 b on a.order_id = b.order_id and a.rn = b.rn
 35  order by order_id;

  ORDER_ID    T1_COL1 T1_COL2    T2_COL1 T2_COL2
---------- ---------- ------- ---------- ----------
         1      10000 deposit       5000 cash
         1      15000 sub fee      10000 card
         1                         10000 bank
         2      10000 deposit
         2       5000 sub fee      15000 cash
         3       5000 fee           3500 card
         3       5000 sub fee       7000 cash
         3       1000 deposit        500 bank

8 rows selected.

SQL>