Re: tough RECURSIVE QUERY, How?

The hierarchical relationship described is one of the reasons why I like Oracle. So far I haven't seen it implemented in another rdbms.

Check the 'CONNECT BY ... STARTING WITH' clause

Cheers,

On Thu, 15 Jan 1998 09:45:28 +1300, "Don Fraser" <don_at_clear.net.nz> wrote:

>I've just done something very similar and am about to attempt the same where
>my table has two parent keys (in the same table).
>
>My answer was to start by joining the table to itself
>'select * from table1, table2'
>then minus the rows that do not belong
>'MINUS
>select * from table1, table2 where parent = parent and child <> child'
>
>Rename the two columns you want from the four available (parent_id,
>child_id).
>
>I'm not sure this is the exact solution for your case but once you have
>joined the table to itself you can see which rows you don't want and exclude
>them in the minus query.
>
>Once you have made the result a view you can query it like
>'select child_id from view where parent_id is whatever' and receive all the
>rows of child_ids for your parent.
>
>This is the view I did where I was concerned with regions within regions.
>
>create or replace view viewname as
>select
> R1.reg_id
> ,R2.reg_id Reg_Family
>from
> V_REG R1
>,V_REG R2
>MINUS
>select
> R1.reg_id
> ,R2.reg_id Reg_Family
>from
>v_reg R1
>,v_reg R2
>WHERE R1.Reg_parent = R2.Reg_parent and R1.Reg_id <> R2.Reg_id
>
>Don Fraser, Aotea Software.
>
>>> 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)
>>>
>

Kaboel Karso

Consultant

                                                           \\\|///
                             \\  ~ ~  //
                             (  @ @  )
==========================-oOOo-(_)-oOOo-===================

eSCOSYS Consultancy		Phone	: +31 (0)32 024 7285
Horst 2827			Fax	: +31 (0)32 024 7286	
8225 MZ Lelystad		Mobile	: +31 (0)65 498 5413
The Netherlands			email	: kkarso_at_knoware.nl

==================================-Oooo.-===================
                         .oooO     (   )
                          (   )      ) /
                           \ (      (_/