In the hierarchical queries, we can depict the parent-child relationship, which exists in a table.
In the following example we have a table with hierarchy of regions.
We will try to find all the regions and sub-regions, which belong to the same parent region, Greece:
with hierarchy_table as (
select 1 as region_id, 'Europe' as region_desc, null as parent_region_id from dual
union
select 2 as region_id, 'North Eastern Europe' as region_desc, 1 as parent_region_id from dual
union
select 3 as region_id, 'Greece' as region_desc, 2 as parent_region_id from dual
union
select 4 as region_id, 'Sterea Hellas' as region_desc, 3 as parent_region_id from dual
union
select 5 as region_id, 'Attiki' as region_desc, 4 as parent_region_id from dual
union
select 6 as region_id, 'Athens' as region_desc, 5 as parent_region_id from dual
union
select 7 as region_id, 'Goudi' as region_desc, 6 as parent_region_id from dual
union
select 8 as region_id, 'Kolonaki' as region_desc, 6 as parent_region_id from dual
union
select 9 as region_id, 'Gkyzi' as region_desc, 6 as parent_region_id from dual
)
SELECT region_desc "Wider Region", CONNECT_BY_ROOT region_desc "Region",
LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(region_desc, '/') "Path"
FROM hierarchy_table
WHERE LEVEL > 1 and parent_region_id = 2
--CONNECT BY PRIOR region_id = parent_region_id;
CONNECT BY PRIOR parent_region_id = region_id;
The result is:
Wider Region Region Level Path
Greece Sterea Hellas 1 /Sterea Hellas/Greece
Greece Attiki 2 /Attiki/Sterea Hellas/Greece
Greece Athens 3 /Athens/Attiki/Sterea Hellas/Greece
Greece Goudi 4 /Goudi/Athens/Attiki/Sterea Hellas/Greece
Greece Kolonaki 4 /Kolonaki/Athens/Attiki/Sterea Hellas/Greece
Greece Gkyzi 4 /Gkyzi/Athens/Attiki/Sterea Hellas/Greece