Getting all descendants of a parent

recursive

I have a table with two columns, Parent and Child.

I 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 |
+----+-----------+

Any ideas how to get all the descendants records of a parent?
I have tried self join using T1.Child = T2.Parent but the logic did not work.

I am using Teiid VDB which doesn't support Recursive WITH clause.

Best Answer

You need a recursive CTE (common table expression):

with    -- recursive
        -- some DBMS (e.g. Postgres) require the word "recursive"
        -- some others (Oracle, SQL-Server) require omitting the "recursive"
        -- and some (e.g. SQLite) don't bother, i.e. they accept both
descendants
  (parent, descendant, lvl) 
as
  ( select parent, child, 1
    from source
  union all
    select d.parent, s.child, d.lvl + 1
    from descendants  d
      join source  s
        on d.descendant = s.parent
  ) 
select *
from descendants 
order by parent, lvl, descendant ;

See the SQLfiddle. The level I added in not needed. It gives you the "distance" between the parent and the descendant, (1 is child, 2 is grandchild, etc).

Link for the related Teiid documentation about: (recursive) CTEs.

PS: For Oracle:

LEVEL is a restricted keyword

Related Solutions

Postgresql – Tree structure hierarchical query sorting

Efficiently storing, processing and reporting on hierarchical data of mixed/unknown depth in SQL is quite a complex area: whole books (such as Joe Celko's "Trees and Hierarchies in SQL for Smarties") have been written on the subject.

What you have there is often called a "naive tree" (only the parent relationship is noted) which does not allow for easily performing such a sort, or for queries where you want all data from under a particular node. It is common to de-normalise the structure in these cases by adding a path element to each row, so you can perform the sort you are looking for by sorting by theis field (and you can perform "everything under no matter how deep" queries by using filters such as tree_path LIKE '/path/to/target/node/%' (or tree_path LIKE '/path/to/target/node%' if you want to include the node itself). This avoids any need for recursion or an arbitrarily long collection joins, though of course you have the extra work to do maintaining this path when the tree changes. The book I usually recommend to all DB people (devs and admins alike), "SQL Antipatterns", has a chapter on this which covers the basics of this and other alternatives quite clearly (so you don't need to try consume Calko's tome unless you have some more complex requirements!).

If you can't alter the structure then you might be able to produce the path as an output using a recursive CTE (Common Table Expression) - I'll not go into detail here as there are already many good examples out there (in the documentation and StackExchange questions such as https://stackoverflow.com/questions/3307480/postgresql-recursive-with).

If your tree has a fixed depth (all leaf nodes are the same number of levels below root) then a fixed set of joins will do the trick:

SELECT {stuff}
FROM   source_table t1
JOIN   source_table t2 ON t2.id=t1.parent
JOIN   source_table t3 ON t3.id=t2.parent
JOIN   source_table t4 ON t4.id=t3.parent
ORDER BY t1.name, t2.name, t3.name, t4.name

for three levels below your root(s). If your depth is not fixed then this would not work (well, it could be forced to up to an arbitrary depth but the joins and the ordering clause would be pretty hairy to design and inefficient to run) to you need to use the CTE approach or use a modified data structure.

Informix – Getting all descendants of a parent

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

Best Answer

Related Solutions

Postgresql – Tree structure hierarchical query sorting

Informix – Getting all descendants of a parent

Related Question