Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

get all grandchildren in hierarchical query

400186Sep 25 2003 — edited Sep 25 2003
I'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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 23 2003
Added on Sep 25 2003
1 comment
918 views