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

How do I achieve that?

Sample data for child_test_table
child_id child_name

111	111 name 
333	333 name

Thanks,
Usha Received on Fri Aug 15 2003 - 15:51:10 CDT