Joining two tables with common primary key but bring in additional columns that is causing cartesian

Question

I'm trying to join two tables similar to below. Is it possible to even join and get data without cartesian products?

Would appreciate any help, Thanks!

Table 1 : Primary Key Column : Field2 : R1 : C1 : R1 : C2 : R2 : C3

Table 2 : Primary Key Column : Field3 : R1 : O1 : R1 : O2 : R3 : O3

Table 3 - joined : Column1 : Column2 : Column3 : R1 : C1 : 01 : R1 : C2 : 02 : R2 : C3 : : R3 : : 03

Hi - unfortunately this isn’t a code writing service. Please provide sample data, the result you are trying to achieve and show the SQL that you have managed to write on your own. Then ask a specific question — NickW, Feb 28 '22 at 23:36
@nickW sorry that wasn't my intent. I'll update my question with the sample query. Thank you for taking the time to respond. — Cindy, Mar 01 '22 at 17:09

score 1 · Accepted Answer · answered Mar 01 '22 at 06:16

Here's one option; read comments within code.

Sample data:

SQL> with
  2  t1 (pk, field2) as
  3    (select 'R1', 'C1' from dual union all
  4     select 'R1', 'C2' from dual union all
  5     select 'R2', 'C3' from dual
  6    ),
  7  t2 (pk, field3) as
  8    (select 'R1', '01' from dual union all
  9     select 'R1', '02' from dual union all
 10     select 'R3', '03' from dual
 11    ),

Query begins here:

 12  --
 13  -- Calculate row number; that new - RN - column will be then used as supplemental JOIN
 14  -- condition
 15  t1_new as
 16    (select pk, field2,
 17            row_number() over (partition by pk order by field2) rn
 18     from t1
 19    ),
 20  t2_new as
 21    (select pk, field3,
 22            row_number() over (partition by pk order by field3) rn
 23     from t2
 24    ),
 25  -- all PK values, as your final result should contain all of them so you can't just
 26  -- use table1 or table2 as none of them contains all PK values
 27  all_pks as
 28    (select pk, rn from t1_new
 29     union
 30     select pk, rn from t2_new
 31    )
 32  -- finally:
 33  select p.pk, a.field2, b.field3
 34  from all_pks p left join t1_new a on a.pk = p.pk and a.rn = p.rn
 35                 left join t2_new b on b.pk = p.pk and b.rn = p.rn
 36  order by p.pk, a.field2, b.field3;

PK FIELD2     FIELD3
-- ---------- ----------
R1 C1         01
R1 C2         02
R2 C3
R3            03

SQL>

Thank you so much @Littlefoot !!! I'll try the above query and will keep you posted if it works :) — Cindy, Mar 01 '22 at 17:10
THANK YOU!! exactly what I was looking for and the query returns the distinct values for the primary key :) — Cindy, Mar 01 '22 at 18:17

Joining two tables with common primary key but bring in additional columns that is causing cartesian

1 Answers1