Informix 11.50 hierarchical SQL
I need a little help with informix 11.50 hierarchy data SQL. I have the following table:
create table cont_reln ( contact char(10), relation_type char(1), related_to char(10)); contact | relation_type | related_to 1000 CH 2001 1000 CH 2002 1000 CH 2003 2001 CH 3001 2001 CH 3002 2002 CH 3003 2003 CH 3004 3004 CH 4001 4001 CH 5001
I've written the two SQLs that can take a contact and figure out all the parents, and all the children:
-- Get the children SELECT contact, related_to, LEVEL FROM cont_reln START WITH contact = '?' CONNECT BY NOCYCLE PRIOR related_to = contact ORDER SIBLINGS BY related_to; -- Get the parents SELECT contact, related_to, LEVEL FROM cont_reln START WITH related_to = '?' CONNECT BY NOCYCLE PRIOR contact = related_to ORDER SIBLINGS BY contact;
Each of these queries return what I'm after, but I'm unsure how to combine them in order to get the following output when I START WITH any contact number... So, no matter what '?' is, if it's somewhere in this hierarchy, the dataset will return exactly the same as below:
contact | relation_type | related_to NULL NULL 1000 1000 CH 2001 2001 CH 3001 2001 CH 3002 1000 CH 2002 2002 CH 3003 2003 CH 3004 3004 CH 4001 4001 CH 5001
I know the first row (with the nulls) would have to be returned as a separate hardcoded return in the SP once I know the root node, but from line 2 -> onwards I'm not sure how to do it.
** EDIT ** The query to find the parent was incorrect - fixed.
OK, I figured it out.
It's not possible to do this in one query, but inside a stored procedure it's trivial.
Firstly, I needed to know the root node (it had nothing to do with combining two queries).
To find the root node I run:
SELECT contact FROM cont_reln WHERE relation_type = 'CH' START WITH related_to = '?' CONNECT BY NOCYCLE PRIOR contact = related_to AND PRIOR related_to != related_to ORDER SIBLINGS BY contact
This is in a foreach, and by virtual of the way the hierarchical query returns, I always know the last record is the root.
From there, I just run the child select based on the root contact id.
SELECT contact, related_to FROM cont_reln WHERE relation_type = 'CH' START WITH contact = 'THE RETURNED ROOT NODE ID' CONNECT BY NOCYCLE PRIOR related_to = contact ORDER SIBLINGS by related_to