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