Re: Hierarchical queries: getting parent-grandparent

Daniel <dlUNSPAMrubin_at_yahoo.com> wrote in message news:<3D33755B.105CC0CD_at_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

To get grandparent-chils tuples:

select p.parent grandparent, c.child grandchild from
( SELECT * FROM mytable
 START WITH parent_id = 1
 CONNECT BY PRIOR child = parent) p,
(SELECT * FROM mytable
 START WITH parent_id = 1
 CONNECT BY PRIOR child = parent) c
where p.child = c.parent

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

It DOES give you all descendats. Maybe you want them all in one line, but it's a different question. You can put reasonable number (20 for example) of descendants into one line using sum/decode/group by tricks. Ever tried to turn report, making lines into columns?

> thanks
>
> (To reply to me directly, please remove the unspam string from my email)
Received on Tue Jul 16 2002 - 16:02:54 CDT