CODEWITHSUNDEEP
sqloracle-databasehierarchical-datarecursive-query
Tom Load
Tom Load

Reputation: 31

Oracle hierarchical query select records along path

Suppose the company hierarchy is like this:

King
-> John
  -> Jack
    -> Chris
    -> Sean
  -> April
-> Jerry
  -> Tom

Given an ancestor e.g. King, and a subordinate, e.g. Chris, is it possible to select all records along the path /King/John/Jack/Chris in one query? i.e. the query will return 4 records - King, John, Jack and Chris?

Table structure: Employee_Name Employee_ID Manager_ID

Manager_ID references to Employee_ID

Upvotes: 0

Views: 104

Answers (2)

Stawros
Stawros

Reputation: 935

with t as
(
    select 'King' as Employee_Name,  1 as Employee_ID, -1 as Manager_ID from dual union all
    select 'John' as Employee_Name,  2 as Employee_ID, 1 as Manager_ID from dual union all
    select 'Jack' as Employee_Name,  3 as Employee_ID, 2 as Manager_ID from dual union all
    select 'Chris' as Employee_Name, 4  as Employee_ID, 3 as Manager_ID from dual union all
    select 'Sean' as Employee_Name,  5 as Employee_ID, 3 as Manager_ID from dual union all
    select 'April' as Employee_Name,  6 as Employee_ID, 2 as Manager_ID from dual union all
    select 'Jerry' as Employee_Name, 7  as Employee_ID, 1 as Manager_ID from dual union all
    select 'Tom' as Employee_Name, 8  as Employee_ID, 7 as Manager_ID from dual
)
select Employee_Name 
 from t
connect by prior Manager_ID = Employee_ID
start with Employee_Name = 'Chris'
order by level desc

Upvotes: 0

dcieslak
dcieslak

Reputation: 2715

I am not sure what your table structure is. In case you store it as paths then the below should work. The query supports multiple records with Chris. It will select all of them.

with test as
  (
  select 1 id, '/King/John/Jack/Chris' str from dual union all
  select 2 id, '/King/John/Jack/April' str from dual union all
  select 3 id, '/King/John/Jack/Sean' str from dual union all
  select 4 id, '/King/Jerry/Tom' str from dual
)
   select id,
          str,
         regexp_substr (str, '[^/]+', 1, rn) split,
          rn
    from test 
    cross
    join (select rownum rn
            from (select max (length (regexp_replace (str, '[^/]+'))) + 1 mx
                    from test
                )
       connect by level <= mx
         ) A
   where regexp_substr (str, '[^/]+', 1, rn) is not null
    and instr(str, 'Chris') > 0 
   order by id, rn
 ;

Here is an example in SQL Fiddle

Example in SQL Fiddle

The trick is cross join that creates multiple rows for each row in main table.

ID  STR SPLIT   RN
1   /King/John/Jack/Chris   King    1
1   /King/John/Jack/Chris   John    2
1   /King/John/Jack/Chris   Jack    3
1   /King/John/Jack/Chris   Chris   4

Upvotes: 0

Related Questions