By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table district(id number(10), name varchar2(20), parent_id number(10))
create table house (id number(10), name varchar2(20), district_id number(10))
insert into district
select 1, 'one', NULL from dual
union all
select 2, 'two', 1 from dual
union all
select 3, 'three', 2 from dual
3 rows affected
insert into house
select 1, 'h1', 3 from dual
union all
select 2, 'h2', 3 from dual
union all
select 3, 'h3', 3 from dual
3 rows affected
select h.id, h.name, d.root_id district_id, d.root_name district_name
from house h
inner join (
select id, root_id, root_name from (
select id, connect_by_root(id) root_id, connect_by_root(name) root_name, connect_by_isleaf is_leaf
from district
start with parent_id is null
connect by prior id = parent_id
)
where is_leaf = 1
) d
on (h.district_id = d.id)
| ID | NAME | DISTRICT_ID | DISTRICT_NAME |
|---|---|---|---|
| 1 | h1 | 1 | one |
| 2 | h2 | 1 | one |
| 3 | h3 | 1 | one |