Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: select hierarchy question
"Peter" <yaphopho_at_yahoo.com> wrote in message
news:0rm7fucgaf7nm63f1h8dg936imvtql6i32_at_4ax.com...
> We have a small SQL-hierargy related problem with our Oracle 7.3
> database. We want to have a *single* query returning all the employees
> working under a certain department taking child-departments into
> account.
>
> Here the simplified tables:
>
> table DEPARTMENT (dep_id number primary key, dep_name varchar2(60),
> dep_parent_id number)
>
> column dep_parent_id refences a dep_id in the same table DEPARTMENT,
> department_id's are random and cannot be changed.
>
> table EMPLOYEE (emp_name varchar2(60) primary key, dep_id number)
>
> The dep_id references the PK of table DEPARTMENT.
>
> The question is: give me all the employees working under department X
>
> so with DEPARTMENTfilled like this:
> 1, company, null
> 2, sales, 1
> 3, marketing, 1
> 4, region a sales, 2
> 5, region b sales, 2
> 6, region a sales big customers, 4
> 7, region a sales small customers, 4
>
> Give me all the employees working under department "sales" results in
> all the employes wokng for departments 2,4,5,6 and 7.
>
> * We tried the connect prior-clause but dit not succeed.
> * The problem is similar to a the question: what are all the system
> privileges of "scott"
>
> Anybody having a suggestion?
>
> Thanks,
> Peter Grasland
>
Can be done in one single statement by putting the connect by in a subquery.
select * from emp
where dep_id in
(select dep_id
from department
connect by dep_parent_id = dep_id -- might be the other way around
start with dep_id = 2
)
Why do you still use 7.3. IIRC the restriction has been lifted in the
meantime.
If this provide insufficient performance, the next best solution is to
denormalize: add an extra column called 'root_department_id' etc, and keep
that correct by using triggers
Hth
-- Sybrand Bakker Senior Oracle DBA to reply remove '-verwijderdit' from my e-mail addressReceived on Tue May 28 2002 - 15:53:45 CDT