sqloracle-databaseoracle11grecursive-queryhierarchical-query

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


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'                     |
+------------------------------------+

Solution

  • 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:

    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