SQL - Creating tree from parent and child columns

Question

I'm having some trouble figuring out how to create a table showing all the relations of a child column with its subsequent parent columns. I am using SQL in Teradata SQL Assistant.

For example, here is the data I have:

Parent | Child                               
A      | B                                                     
A      | C         
B      | D           
E      | E

I want to be able to get an output table, showing the lowest level child element with all its subsequent parent elements as follows:

Child | ParentL1 | Parent L2        
C     | A       
D     | B        | A       
E

Thing is, I do not know how many levels of parents exists in my data, and I am allowed access only to querying data, so I am unable to create new tables, alter this table, or change any values.

Is there any way to get the expected output in a query?

Thank you!

Answer 1

Something like this (tested with PostgreSQL as I don't have Teradata available):

with recursive tree as (

    select parent, child, child||'/'||parent as path
    from foo
    where child not in (select parent from foo)
    or parent = child

    union all

    select c.parent, c.child, p.path||'/'||c.parent
    from foo c
      join tree p on c.child = p.parent
    where c.parent <> c.child
)
select path
from tree
order by parent;