Re: tough RECURSIVE QUERY, How?

NNOOR wrote in message <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)

Hey Nasir,
Something like this should do the trick

select office_id, parent_off_id from office start with parent_off_id = 20 // <---- or whatever connect by prior office_id = parent_off_id minus
select office_id, parent_off_id from office off where exists (select 1 from office where parent_off_id = off.office_id)

It does the recursion for you in the first set and then subtracts off from the result all the offices that are parents to any other office. Presumably this would leave all the 'leaf' offices. This is just off the top of my head and I now wonder if it works for the special case when the office you are checking is a leaf (as in your case 25) but you can handle that hopefully programmatically (assuming you are writing embedded SQL or PL/SQL) if the result returns 0 rows.

Also, there are probably variations of subtracting or adding sets that you might be able to experiment with but this example might at least get you started!

I hope that helps Nasir. Good luck. Received on Wed Jan 14 1998 - 00:00:00 CST