Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: tough RECURSIVE QUERY, How?

Re: tough RECURSIVE QUERY, How?

From: Don Fraser <don_at_clear.net.nz>
Date: 1998/01/15
Message-ID: <69j8f0$t6j$1@titania.telstra.net.nz>#1/1

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)
>>
Received on Thu Jan 15 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US