user3244615 user3244615 - 6 months ago 22
SQL Question

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

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.

Comments