By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table district(
id integer primary key,
name varchar2(32),
parent_id integer references district(id)
);
insert into district (id, name, parent_id)
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
create table house(
id integer primary key,
name varchar2(32),
district_id integer references district(id)
);
insert into house (id, name, district_id)
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
with d (root_id, root_name, id) as (
select connect_by_root(id), connect_by_root(name), id
from district
start with parent_id is null
connect by parent_id = prior id
)
select
h.id,
h.name,
d.root_id,
d.root_name
from house h
join d on d.id = h.district_id;
| ID | NAME | ROOT_ID | ROOT_NAME |
|---|---|---|---|
| 1 | h1 | 1 | one |
| 2 | h2 | 1 | one |
| 3 | h3 | 1 | one |