Re: tough RECURSIVE QUERY, How?

Here is one potential solution:

Create an SQL script file (e.g. test.sql) containing the following code:

select id from test where pid in (select pid from test start with pid = &1

   connect by prior id = pid) and id not in (select distinct pid from test) union
select id from test where id = &1 and id not in (select distinct pid from test);

Then execute the script within SQL (e.g. @test.sql 20)

I tested this against your suggested table below and also added a third level of sub-office where parent office = 26 and office = 28. Result always contained only the lowest level offices.

NNOOR <Nnoor_at_cris.com> wrote in article
<69in86$j7v_at_examiner.concentric.net>...
>
> Hi,
> Following is the scenerio:
>
> An office can have one or more sub-offices. Each of these sub-offices
> can have one or more sub-sub-offices...so on. I have implemented this
> in a single table.
> e.g.
> PARENT_OFF_ID OFFICE_ID
> 20 21
> 20 22
> 21 23
> 24 25
> 23 26
> 23 27 etc etc
>
> For a given office_id, the query should return either just the office_id
> passes as the parameter (if it happens to have NO sub offices) or ALL of
> the LOWEST level offices.
>
> e.g. if passed, 25, it should return 25.
> if passed 20, it should return 22,26,27 (notice NOT 21 and 23)
> if passed 21, it should return 26,27
> if passed 23, it shoudl return 26,27
>
> Can anyone help me with this? Can it be done just thru SQL?
>
> Thanks and Regards,
> Nasir (nnoor_at_cris.com)
>
Received on Wed Jan 14 1998 - 00:00:00 CST