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 district
s 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' |
+------------------------------------+
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:
with
clause)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