By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1 (
id number,
nv varchar2(2),
sid varchar2(3),
sid2 varchar2(3),
sid3 varchar2(3),
sid4 varchar2(3)
)
create table table2 (
id number,
nv varchar2(2),
sid varchar2(3),
sid2 varchar2(3),
sid3 varchar2(3),
sid4 varchar2(3)
)
insert into table1
select 1, 'a', '11','A1','B1','C1' from dual union all
select 2, 'b', '22','A2','B2','' from dual union all
select 3, 'c', '33','A3','B3','C3' from dual
3 rows affected
insert into table2
select 4, 'x', '11','','B1','C1' from dual union all
select 5, 'y', '','A2','B2','' from dual union all
select 6, 'z', '33','A3','','C3' from dual
3 rows affected
with t1 as (
select id, nv, sid, sid2, sid3, sid4,
id || nv as catid, id || nv as origid
from table1
)
select id, nv, sid, sid2, sid3, sid4, catid, origid
from t1
union all
select id, nv, sid, sid2, sid3, sid4, id || nv as catid,
(select t1.origid
from t1
where (t1.sid = t2.sid or t1.sid2 = t2.sid2 or
t1.sid3 = t2.sid3 or t1.sid4 = t2.sid4
) and
rownum = 1
) as origid
from table2 t2
| ID | NV | SID | SID2 | SID3 | SID4 | CATID | ORIGID |
|---|---|---|---|---|---|---|---|
| 1 | a | 11 | A1 | B1 | C1 | 1a | 1a |
| 2 | b | 22 | A2 | B2 | null | 2b | 2b |
| 3 | c | 33 | A3 | B3 | C3 | 3c | 3c |
| 4 | x | 11 | null | B1 | C1 | 4x | 1a |
| 5 | y | null | A2 | B2 | null | 5y | 2b |
| 6 | z | 33 | A3 | null | C3 | 6z | 3c |