Informix 11.50 hierarchical SQL

Question

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.

Any ideas?

** EDIT ** The query to find the parent was incorrect - fixed.

Trent Trent · Accepted Answer · 2014-01-31T06:13:33

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

Informix 11.50 hierarchical SQL

1 Answers