Opposite of CONNECT BY PRIOR

Hi,

Last week I posted a question related to CONNECT BY PRIOR and got a prompt reply from Culonbu. Thanks for the same. Now I am looking for a query which does exactly the opposite. Given a top most node, I would like to get all its children. Following are the details.

I have the following data in my employee table.

Boss_ID Employee_ID
-------- -----------
-1 100

100 201
100 202
100 203

201 301
201 302
201 303

202 401
202 402
202 403

203 501
203 502
203 503

301 601
302 602

601 701
601 702

Boss_ID -1 means, the corresponding Employee is the top most with no boss.

I am looking for a query that gives me the following:

Given an Boss_ID, I want a list of all the employees under him

For eg:

For Boss_ID 100, my query should return the following

201
301
302
303

202
401
402
403

203
501
502
503

301
601

302
602

601
701
702

For Boss_ID 202, my query should return the following

202
401
402
403

I appreciate your help in advance.

Regards,
Desi Rookie

Is this your assignment / home work ? :cool:

Any way , you should understand how PRIOR syntax work.

PRIOR means PARENT of

select field61 || ' - ' || field62
from foo_1
start with field61 = '202'
connect by prior field62= field61

should give the answer you want.