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

> 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

-- 
  Rene Nyffenegger
  www.adp-gmbh.ch