Teradata Recursive SQL

Database
Enthusiast

Teradata Recursive SQL

Hi All,

I'm new to teradata and your help here would be greatly appreaciated.I need a recursive SQL to find the child-parent relationship in a table.For example,

Child Parent

B A
C B
D B
G C
J A

Now i need to recursively find out the parents until a node does not have any parents(I need to do this search recursively for 5 levels)

For instance,If i want to trace the parent of child G...it must be like G-->C-->B-->A
If i want to know the parent of G,it must traverse like D-->B-->A.

Regards,
Kumar
1 REPLY
Enthusiast

Re: Teradata Recursive SQL

I haven't tested... but something like this should work ...

WITH RECURSIVE RECTBL(CHLD, PRNT, LVL)
AS
(

SELECT CHILD, PARENT, 1(INTEGER)
FROM MYTBL
WHERE CHILD = 'G'

UNION ALL

SELECT MYTBL.CHILD, MYTBL.PARENT, RECTBL.LVL + 1
FROM RECTBL INNER JOIN MYTBL
ON RECTBL.PRNT = MYTBL.CHILD
AND RECTBL.LVL <= 5

)
SELECT CHLD, PRNT
FROM RECTBL
ORDER BY LVL
;