How to get root of hierarchial joined data in oracle 11g

Question

My schema looks like this.

I've got hierarchial districts:

create table district(
  id integer primary key,
  name varchar2(32),
  parent_id integer references district(id)
)

And houses within districts:

create table house(
  id integer primary key,
  name varchar2(32),
  district_id integer references district(id)
)

house.district_id is always at the bottom of district's hierarchy. How do I select every house and id and name of the root of districts hierarchy?

For now I'm using two subqueries, but it doesn't feel right:

select 
  h.id, 
  h.name,
  (
    select id from district
     where parent_id is null
     start with id = house.district_id
   connect by parent_id = id
  ) as district_id,
  (
    select name from district
     where parent_id is null
     start with id = house.district_id
   connect by parent_id = id
  ) as district_name
from house;

Oracle version is 11g Release 2.

Sample data: Districts

+-------------------+
| id name parent_id |
+-------------------+
| 1 'one' NULL      |
| 2 'two' 1         |
| 3 'three' 3       |
+-------------------+

Houses

id name district_id
1 'h1' 3
2 'h2' 3
3 'h3' 3

Desired output:

+------------------------------------+
| id name district_id, district_name |
+------------------------------------+
| 1 'h1' 1 'one'                     |
| 2 'h2' 1 'one'                     |
| 3 'h3' 1 'one'                     |
+------------------------------------+

Answer 1

I like to use a recursive with clause for this. This feature is supported in Oracle starting version 11gR2, which you are using. I find that it is worth learning this new syntax as compared to the connect by queries, because:

• it is based on the standard SQL specification (all other major databases support recursion in the with clause)
• it is somehow more flexible that Oracle-specific methods

Consider:

with cte (id, parent_id, root_id, root_name) as (
    select id, parent_id, id as root_id, name as root_name
    from district
    where parent_id is null
    union all
    select d.id, d.parent_id, c.root_id, c.root_name
    from   cte c
    inner join district d on d.parent_id = c.id
) search depth first by id set order1
select h.id, h.name, c.root_id, c.root_name
from house h
inner join cte c on c.id = h.district_id

Answer 2

Rather than have a correlated subquery (or two), you could get all district IDs with their roots - using connect_by_root() - and then join to that:

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;

db<>fiddle

You could also use a recursive CTE instead of a hierarchical query.

Answer 3

You can use connect_by_root to get root district for all sub-districts and connect_by_isleaf to leave only bottom level districts in data

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)

fiddle