Skip to content
Advertisement

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 :

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 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?

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

which will return:

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:

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

Advertisement

Answer

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

This also seems to work fine.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement