Re: sql question - hierarchy

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> wrote in message news:b5s5k0$net$1_at_babylon.agtel.net...
> > Actually, I have solved the problem, using function that is returning
> > master_org_id of currnet_org_id. What I was asking was, is there a
simple
> > and better solution to solve this. Of course, without using a function,
> > query would be faster and better and that is the query that I'm looking
for.
> > So, if you have a that kind of solution, please post it, otherwise,
please
> > don't write answers like this one...
> > Thanks....
>
> All you had to do is join ORGANIZATION to itself, and for each row
> in left table, get all rows from the right table, which are under the
> left row in the hierarchy. Additional requirement of every master
> being its own child can be satisfied by simple equation. Here's the
> resulting query:
>
> SQL> SELECT m.NAME master_org_id,
> 2 c.NAME org_id_current
> 3 FROM ORGANIZATION m, /* master */
> 4 ORGANIZATION c /* children */
> 5 WHERE
> 6 m.id = c.id /* master is its own child */
> 7 OR c.id IN (SELECT org_id
> 8 FROM HIERARCHY
> 9 START WITH org_id_parent = m.id /* start with current master
row */
> 10 CONNECT BY PRIOR org_id = org_id_parent /* and get all its
children */
> 11 );
>
> MASTER_ORG_ID ORG_ID_CURRENT
> ------------- --------------
> A1 A1
> A1 B1
> A1 B2
> A1 C1
> A1 C2
> B1 B1
> B1 C1
> B1 C2
> B2 B2
> C1 C1
> C2 C2
>
> 11 rows selected.
>
> --
> Vladimir Zakharychev (bob_at_dpsp-yes.com)
http://www.dpsp-yes.com
> Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet
applications.
> All opinions are mine and do not necessarily go in line with those of my
employer.
>

Thanks, it looks simple, works faster, and not using function. I thought that you didn't understand my question. I am trying to solve this problem for two days and now, when you gave me the solution, I can't belive that is so simple. Thanks again! Received on Wed Mar 26 2003 - 15:21:50 CET