|
|
|
Forum Newbie
Group: Forum Members
Last Login: 5/8/2008 5:25:30 AM
Posts: 8,
Visits: 6
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 12:24:48 AM
Posts: 425,
Visits: 389
|
|
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
;
|
|
|
|