HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Informix - Getting all descendants of a parent

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
descendantsallinformixparentgetting

Problem

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?

Solution

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

Code Snippets

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

Context

StackExchange Database Administrators Q#156875, answer score: 3

Revisions (0)

No revisions yet.