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

Question

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 1

This combines your two queries into one and seems to work:

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

Basically, this takes your query that works up the hierarchy and runs it, then filters the result to get the top manager (the employee with no manager), and uses that value as the START in the 'hierarchic descent from top' query.

5148
5149    5148
5150    5149
5151    5148
5152    5151
5154    5148
5155    5154

I get the same result with any starting value: 5148, 5149, 5150, 5151, 5152, 5154, 5155.

Answer 2

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.

Answer 3

Instead of using a hierarchical query, you can use a simple merge statement with the help of a temp table to achieve the desired result in informix 12 and above.

Create the temp table using the below stmt:

select a.employee_id,
       a.manager_id,
       rpad(a.manager_id, 100, ' ') as manager_hier,
       a.manager_id as topmanager
from empl_relation a
left join empl_relation b on a.manager_id = b.employee_id into temp emp_mgr_rel;

Run the below merge stmt as many times as the depth of the longest branch of the tree. Running it more than that does not impact the end result so no worries:

merge into emp_mgr_rel as a using emp_mgr_rel as b on a.topmanager = b.employee_id WHEN MATCHED THEN
UPDATE
set a.manager_hier = nvl(trim(a.manager_hier), '') || '-' || nvl(trim(b.topmanager), ''),
    a.topmanager = trim(b.manager_hier);

Check your result using the below stmt. You will see the hierarchy as a hyphenated value under the manager_hier column:

select employee_id, manager_hier from emp_mgr_rel;