I have a table with parent/child ids, and I'm trying to get a full list of all levels of parents AND children for a given id.
Basically, for a given id, go all the way down and all the way up the hierarchy.

I've tried connect by, but maybe a recursive CTE would be better?

select 'abc' as child, null as parent from dual union all
select 'mno' as child, 'abc' as parent from dual union all
select 'def' as child, 'abc' as parent from dual union all
select '123' as child, 'abc' as parent from dual union all
select 'qrs' as child, '123' as parent from dual union all
select '789' as child, 'def' as parent from dual union all
select 'xyz' as child, '123' as parent from dual 

Ex:

For 123, the desired output:

• xyz > 123 > abc
• qrs > 123 > abc

For abc, the desired output:

• xyz > 123 > abc
• 789 > def > abc
• qrs > 123 > abc
• mno > abc

Here's my attempt. It seems kind of hacky with the full_hier being a concatenation substring of the child & parent paths. Plus, I'm getting additional results that I'm not sure how to filter out (Ex: def > abc is returned though I don't want it as it's captured in 789 > def > abc).

select 
    connect_by_root child,
    substr(sys_connect_by_path(child, '>' ),2) as child_hier
    , substr(sys_connect_by_path(parent, '>' ),2) as parent_hier
    , case 
        when parent is null then substr(sys_connect_by_path(child, '>' ),2)
        else substr(sys_connect_by_path(child, '>' ),2) ||  substr(substr(sys_connect_by_path(parent, '>' ),2), instr(substr(sys_connect_by_path(parent, '>' ),2),'>',1,1)) 
    end as full_hier
    , level
from   
    (
        select 'abc' as child, null as parent from dual union all
        select 'mno' as child, 'abc' as parent from dual union all
        select 'def' as child, 'abc' as parent from dual union all
        select '123' as child, 'abc' as parent from dual union all
        select 'qrs' as child, '123' as parent from dual union all
        select '789' as child, 'def' as parent from dual union all
        select 'xyz' as child, '123' as parent from dual 
    ) table_name
where 1=1
--and connect_by_isleaf = 1
--and connect_by_root child in ('123')
and child = 'abc'
connect by child = prior parent 
--connect_by prior parent = child

Thanks for taking a look, I appreciate it!

CodePudding user response：

If I understand correctly, given any id (as an input - below I use a bind variable in the query), you need to find all its leaf descendants, and then for each such leaf, show the full path from leaf to the root of the hierarchy.

One way to do that is to traverse the hierarchy twice: first start from the given id and find all its leaf descendants, then traverse in the opposite direction to find all the "full paths".

While this may look (marginally) more elegant, it will be significantly less efficient. The better approach is along the lines you were trying already.

Below I use the with clause (and give column names in the subquery declaration - that is only supported since Oracle 11.2, if your version is 11.1 you will need to move the aliases into the select clauses as you were doing in your attempt).

with
  table_name (child, parent) as (
    select 'abc', null  from dual union all
    select 'mno', 'abc' from dual union all
    select 'def', 'abc' from dual union all
    select '123', 'abc' from dual union all
    select 'qrs', '123' from dual union all
    select '789', 'def' from dual union all
    select 'xyz', '123' from dual   
  )
, a (ancestor_path) as (
    select  sys_connect_by_path(child, '>')
    from    table_name
    where   connect_by_isleaf = 1
    start   with child = :i_child
    connect by child = prior parent
  )
, d (descendant_path) as (
    select  substr(sys_connect_by_path(child, '>'), 2)
    from    table_name
    where   connect_by_isleaf = 1
    start   with parent = :i_child
    connect by parent = prior child
  )
select d.descendant_path || a.ancestor_path as full_path
from   d cross join a
;

CodePudding user response：

Here's a method that first descends down the tree to get the root parent(s) from the chosen one.

Then uses those roots to climb back up.

The resulting paths are then filtered on the chosen child.

And what remains are those where the final child isn't a parent.

create table test_hierarchy (
 child varchar(3),
 parent varchar(3),
 primary key (child)
);

insert into test_hierarchy (child, parent)
select 'abc' as child, null as parent from dual union all
select 'mno' as child, 'abc' as parent from dual union all
select 'def' as child, 'abc' as parent from dual union all
select '123' as child, 'abc' as parent from dual union all
select 'qrs' as child, '123' as parent from dual union all
select '789' as child, 'def' as parent from dual union all
select 'xyz' as child, '123' as parent from dual;

with cte (base) as (
 select '123' from dual
)
select *
from
(
  select 
    sys_connect_by_path(child,'>') as path
  , cte.base
  , level
  , connect_by_root child as child
  from test_hierarchy
  cross join cte
  where child in (
    select connect_by_root child 
    from test_hierarchy
    where child in (select base from cte)
    and connect_by_root parent is null
    connect by prior child = parent 
  ) 
  connect by prior parent = child
) q
where path like '%'||base||'%'
  and not exists (
    select 1 
    from test_hierarchy t
    where t.parent = q.child
  ) 

Demo on db<>fiddle here