Getting all descendants of a parent

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.

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

StackExchange Database Administrators Q#94932, answer score: 24