get all grandchildren in hierarchical query
400186Sep 25 2003 — edited Sep 25 2003I'm trying to write a query on oracle 9.2.0 that
will give me all
grandparent-child assocations. For example, if
this is my table:
parent child
1 2
2 3
3 4
I want a query that shows all descendant of
"parent". Such a query
should produce:
parent child
1 2
2 3
3 4
1 3
1 4
2 4
I've tried using oracle hierarchical queries
(START and CONNECT BY), but
they don't give me all grandparent-child tuples. For
example, here's what I get
with this query:
SELECT * FROM mytable
START WITH parent_id = 1
CONNECT BY PRIOR child = parent;
PARENT CHILD
---------- ----------
1 2
2 3
3 4
Suggestions?
Thanks.