I need a little help with Informix hierarchical sql query. I have table with the following structure : I am able to run the following query successfully : which returns the exact hierarchy as specified in the table above. However, I am trying to achieve something different here. I am trying to get the same result-set given any employee id

Given any child in the hierarchy, fetch complete tree by INFORMIX hierarchical SQL

I need a little help with Informix hierarchical sql query. I have table with the following structure :

create table empl_relation (
employee_id char(10),
manager_id char(10));

employee_id      |   manager_id
 5148                null              
 5149                5148
 5150                5149
 5151                5148
 5152                5151
 5154                5148
 5155                5154

I am able to run the following query successfully :

SELECT employee_id, manager_id FROM empl_relation  
    START WITH employee_id = 5148 
    CONNECT BY PRIOR employee_id = manager_id 
    ORDER  SIBLINGS BY employee_id;

which returns the exact hierarchy as specified in the table above. However, I am trying to achieve something different here. I am trying to get the same result-set given any employee id in the hierarchy as the input. For example, in the query, if I specify 5154 as the input employee_id, I should be able to get all the parents and their children and the children and grand-children of the input employee id. To be precise , I want the exact same result-set as I got by running the above mentioned query.

Is it possible to achieve in a single query? If yes, can you please help me in achieving this?

                         EDIT

Ok, I have figured one way to achieve this, but it involves executing 2 queries as follows :

SELECT employee_id, manager_id FROM empl_relation
    START WITH employee_id = 5150 
    CONNECT BY employee_id = PRIOR manager_id 
    ORDER   SIBLINGS BY employee_id ;

which will return:

employee_id      |   manager_id
5148    
5149                  5148
5150                  5149

Then we can retrieve the parent employee_id on the application layer by iterating through the result-set and then executing the below query to fetch the complete hierarchical tree:

SELECT employee_id, manager_id FROM empl_relation  
    START WITH employee_id = 5148 
    CONNECT BY PRIOR employee_id = manager_id 
    ORDER  SIBLINGS BY employee_id;

This will work fine, but it would really be great if I can achieve this in a single query.

Answer

Inspired by Jonathan’s reply, I came up with a little bit shorter version of his query as follows

SELECT employee_id,manager_id FROM empl_relation
START WITH employee_id =
 (SELECT employee_id
   FROM empl_relation er
   WHERE er.manager_id IS NULL
   START WITH employee_id = 5150 CONNECT BY employee_id =
   PRIOR manager_id) 
 CONNECT BY
 PRIOR employee_id = manager_id
 ORDER BY employee_id;

This also seems to work fine.

Advertisement

Answer