Re: Hierarchical Queries- How to elimnate some of the children rows and all of branch below it

Hi Rene,

This will work if I have a child of 111, 112 etc. with a parent of 11. But In my case it may not be like that.
A parent of 11 can have children with IDs of 999, 1234 or any number that will be generated randamly. How will I do it then?

Thanks
Usha

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<bhm76q$th76$1_at_ID-82536.news.uni-berlin.de>...
> > Hi,
> > I have a table, which has Hierarchical data, and I can get all of data
> > using Hierarchical Queries START WITH and CONNECT BY.
> >
> > Example:
> > SELECT child_id from link_table
> > START WITH parent_id=1
> > CONNECT BY PRIOR child_id=parent_id;
> >
> > But the problem I have is; I need to eliminate some of the children
> > and all of it's children from the result set depending on a condition.
> >
> > For example: If I have the following data in link_table
> > paren_id child_id
> > 1 11
> > 1 12
> > 1 13
> > 11 111
> > 11 112
> > 12 121
> > 111 1111
> > 111 1112
> >
> > I get following if I do not have any condition with above query:
> > child_id
> > 11
> > 12
> > 13
> > 111
> > 112
> > 121
> > 1111
> > 1112
> >
> > But I need to eliminate 111 and its children - 1111, 1112 from the
> > result set
> > If child_id of 111 is exist in another table. Ex: (child_test_table)
> > child_id
> > 11
> > 12
> > 13
> > 112
> > 121
>
>
> This should do the trick:
>
> create table link_table (
> parent_id number,
> child_id number
> );
>
> insert into link_table values ( 1, 11);
> insert into link_table values ( 1, 12);
> insert into link_table values ( 1, 13);
> insert into link_table values ( 11, 111);
> insert into link_table values ( 11, 112);
> insert into link_table values ( 12, 121);
> insert into link_table values ( 111,1111);
> insert into link_table values ( 111,1112);
> insert into link_table values (1112,2112);
>
> create table child_test_table (
> exclude_id number
> );
>
> insert into child_test_table values (111);
> insert into child_test_table values ( 42);
>
>
> select
> child_id
> from (
> select
> child_id,
> parent_id,
> exclude_id
> from
> link_table,
> child_test_table
> where
> exclude_id(+) = child_id
> )
> start with
> parent_id = 1
> connect by
> prior child_id = parent_id and
> exclude_id is null;
>
>
> hth
> Rene
Received on Wed Aug 20 2003 - 17:24:08 CDT