Opposite of CONNECT BY PRIOR
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Opposite of CONNECT BY PRIOR

  1. #1
    Join Date
    Jun 2005
    Posts
    11

    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

  2. #2
    Join Date
    Dec 2000
    Posts
    126
    Is this your assignment / home work ?

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width