CODEWITHSUNDEEP
sqldatabaseoracle-databasehierarchical-data
Red_Box
Red_Box

Reputation: 50

How to get rows matching a condition using a hierarchical query

Consider the following query from Oracle documentation https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm

SELECT employee_id, last_name, manager_id
   FROM employees
   CONNECT BY PRIOR employee_id = manager_id;

EMPLOYEE_ID LAST_NAME                        MANAGER_ID

    101     Kochhar                          100
    108     Greenberg                        101
    109     Faviet                           108
    110     Chen                             108
    111     Sciarra                          108
    112     Urman                            108
    113     Popp                             108
    200     Whalen                           101

I want to filter this tree to get employees only with a letter 'a' in a last name. I could use WHERE clause, but the thing is I don't want to get only the rows that do match condition, but also their parents event if they DON'T, i.e I don't want to break a tree. According documentation Oracle evaluates the condition for each row individually. For example if I use WHERE clause I get rows with ids 101, 109, 111, 112, 200. But I want to get 101, 108, 109, 111, 112, 200. How can I filter the tree without breaking it?

Upvotes: 1

Views: 130

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

As one of the approaches, you can start traversing the tree from the bottom up - you find an employee with an a in his/her name and go up the tree:

Distinct clause is there to get rid of duplicate parents and we need second connect by clause to turn the tree upside down.

 -- sample of data from your question
 with t1(EMPLOYEE_ID,LAST_NAME,MANAGER_ID) as(
   select 101, 'Kochhar'   ,  100 from dual union all
   select 108, 'Greenberg' ,  101 from dual union all
   select 109, 'Faviet'    ,  108 from dual union all
   select 110, 'Chen'      ,  108 from dual union all
   select 111, 'Sciarra'   ,  108 from dual union all
   select 112, 'Urman'     ,  108 from dual union all
   select 113, 'Popp'      ,  108 from dual union all
   select 200, 'Whalen'    ,  101 from dual
 )
-- actual query 
select employee_id
      , manager_id
      , concat(lpad('-', 3*level, '-'), last_name) as last_name
  from (
        -- using distinct to get rid of duplicate parents 
        select distinct last_name 
              , employee_id
              , manager_id
          from t1
          start with last_name like '%a%'
         connect by   employee_id  =  prior manager_id 
     ) q
   start with manager_id = 100
 connect by prior employee_id = manager_id

Result:

  EMPLOYEE_ID MANAGER_ID LAST_NAME           
----------- ---------- --------------------
        101        100 ---Kochhar          
        108        101 ------Greenberg     
        109        108 ---------Faviet     
        111        108 ---------Sciarra    
        112        108 ---------Urman      
        200        101 ------Whalen        

6 rows selected.

Upvotes: 1

Related Questions