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 address