3

I have a table with two columns, Parent and Child. Need to get the list of all descendants associated with the parent records.

Source Table:

+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |
|  b     |     c |
|  c     |     d |
|  d     |     e |
|  e     |     f |
|  f     |     x |
+----+-----------+

Expected Result:

+----+-----------+
| Parent | Child |
+----+-----------+
|  a     |     b |  // As b is the child of a, all the descendants of b 
|  a     |     c |  // are also descendants of a. 
|  a     |     d |
|  a     |     e |
|  a     |     f |
|  a     |     x |
|  b     |     c |  // As c is the child of b, all the descendants of c 
|  b     |     d |  // are also descendants of b.
|  b     |     e |
|  b     |     f |
|  b     |     x |
|  c     |     d |
|  c     |     e |
|  c     |     f |
|  c     |     x |
|  d     |     e |
|  d     |     f |
|  d     |     x |
|  e     |     f |
|  e     |     x |
|  f     |     x |
+----+-----------+

Informix 11.50 doesn't have recursive CTEs (common table expressions). CONNECT BY can help about recursion levels, paths etc., but I can not get expected result using CONNECT BY. Any idea?

3

Using the CONNECT BY syntax (similar to Oracle's) which allows recursive queries:

select connect_by_root parent as ancestor, 
       child
from x                            -- table name
connect by prior child = parent
order siblings by parent ;

Tested in rextester.com

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.