Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Hierarchical queries: getting parent-grandparent

Re: Hierarchical queries: getting parent-grandparent

From: Daniel <dlUNSPAMrubin_at_yahoo.com>
Date: Tue, 16 Jul 2002 17:01:03 -0700
Message-ID: <3D34B3BF.73B38FF9@yahoo.com>


Thanks for your suggestion! One question in response below--you say you can put reasonable number (20 for example) of descendants into one line using sum/decode/group by tricks, e.g., making lines into columns. I'm not familiar with this. Do you have an example of this or pointer to where I can see how to do this?

Thanks,
Daniel

(To reply to me directly, please remove the UNSPAM part from my email)

Alex Filonov wrote:

> 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 - 19:01:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US