Re: sql question - hierarchy

> 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....

[Quoted] 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 */
[Quoted]  10           CONNECT BY PRIOR org_id = org_id_parent /* and get all its children */
 11        );

[Quoted] 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
[Quoted] Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
[Quoted] All opinions are mine and do not necessarily go in line with those of my employer.