-1

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

Cosanostraave
  • 1
  • 1
  • 1
    [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [More.](https://meta.stackexchange.com/q/320052/266284) [mre] [ask] [Help] PS PS A "basically" or "essentially" or "in other words" that doesn't introduce or summarize a clear, precise & full description that you also give just means "unclearly" or "it is false that". When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS Show research, what can you do? – philipxy Aug 03 '22 at 07:19
  • [Two SQL LEFT JOINS produce incorrect result](https://stackoverflow.com/q/12464037/3404097) – philipxy Aug 03 '22 at 07:23

1 Answers1

-1

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>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57