Recursion In Oracle
Solution 1:
WITH Ancestor(arg1, arg2) AS
(
SELECT p.arg1, p.arg2
FROM parent p
WHERE arg2 NOT IN
(
SELECT arg1
FROM parent
)
UNION ALL
SELECT p.arg1, a.arg2
FROM Ancestor a
JOIN parent p
ON p.arg2 = a.arg1
)
SELECT *
FROM Ancestor
Oracle
only supports recursive CTE
since 11g
Release 2.
In earlier versions, use CONNECT BY
clause:
SELECT arg1, CONNECT_BY_ROOT arg2
FROM parent
START WITH
arg2 NOT IN
(
SELECT arg1
FROM parent
)
CONNECT BY
arg2 = PRIOR arg1
Solution 2:
Oracle allows recursive queries. See: http://www.adp-gmbh.ch/ora/sql/connect_by.html
Of course, these usually assume the hierarchical data is all in one table. Splitting it into separate tables makes things complicated.