Hierarchical queries: getting parent-grandparent

From: Daniel <dlUNSPAMrubin_at_yahoo.com>
Date: Mon, 15 Jul 2002 18:22:35 -0700
Message-ID: <3D33755B.105CC0CD@yahoo.com>

I'm trying to write a query on oracle 8.1.7 that will give me 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 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

  But this doesn't give all descendants. What query do I need to write to get all
  descendants?
  thanks

(To reply to me directly, please remove the unspam string from my email) Received on Mon Jul 15 2002 - 20:22:35 CDT