Чисто из любви к искусству:
with
t1 as
( select 1 t1_id, 'name1' t1_name, 'tag1' t1_tag from dual union all
select 2, 'name2', 'tag2' from dual union all
select 3, 'name3', 'tag3' from dual union all
select 4, 'name4', 'tag4' from dual union all
select 5, 'name2', 'tag2' from dual union all
select 6, 'name3', 'tag3' from dual union all
select 7, 'name5', 'tag5' from dual union all
select 8, 'name2', 'tag2' from dual
),
t2 as
( select 1 t2_id, 1 t2_main_id, 'name2' t2_name, 'tag2' t2_tag from dual union all
select 2, 1, 'name3', 'tag3' from dual union all
select 3, 2, 'name1', 'tag1' from dual union all
select 4, 2, 'name2', 'tag2' from dual union all
select 5, 2, 'name3', 'tag3' from dual union all
select 6, 3, 'name2', 'tag2' from dual union all
select 7, 3, 'name3', 'tag3' from dual union all
select 8, 3, 'name5', 'tag5' from dual union all
select 9, 3, 'name2', 'tag2' from dual union all
select 10, 4, 'name2', 'tag9' from dual
),
--
tt1 as (select t1.*, row_number() over (order by t1_id) as t1_rn from t1),
tt2 as (select t2.*, row_number() over (partition by t2_main_id order by t2_id) as t2_rn from t2),
ttt1 as
( select connect_by_root t1_id as t1_id, sys_connect_by_path(t1_name||'^'||t1_tag, '\') as t1_path
from tt1, (select max(count(*)) as max_level from tt2 group by t2_main_id)
connect by t1_rn = prior t1_rn + 1 and level <= max_level
),
ttt2 as
( select t2_main_id, max(sys_connect_by_path(t2_name||'^'||t2_tag, '\')) as t2_path
from tt2
start with t2_rn = 1
connect by t2_rn = prior t2_rn + 1 and t2_main_id = prior t2_main_id
group by t2_main_id
)
select distinct t2_main_id, t1_id
from ttt1, ttt2
where t1_path like t2_path || '%'
order by 1, 2
;
T2_MAIN_ID T1_ID
------------- -------------
1 2
1 5
2 1
3 5Но реальные данные наверняка не пролезут в ограничения :)