Post reply

Parent child relation qry

  • November 7, 2005 at 6:42 pm

    #166622

    Hi guys,

    please use the following script:

    create table emp

    (

    mgr_name varchar(50),

    emp_name varchar(50)

    )

    insert into emp values ('sonia','manmohan')

    insert into emp values ('manmohan','arjunsingh')

    insert into emp values ('manmohan','natwar')

    insert into emp values ('manmohan','chidambaram')

    insert into emp values ('chidamabram','palanimanickam')

    insert into emp values ('natwar','prabhu')

    insert into emp values ('manmohan','laloo')

    insert into emp values ('laloo','r.t.velu')

    Suppose I need all the employees working under 'manmohan', I should get the following people : arjunsingh,natwar,chidambaram,laloo,prabhu,palanimanickam,r.t.velu

    I need a simple SQL solution and a BIG NO to cursors and stored procedures.

    Is this possible?

    Hari

    Kindest Regards,

    Hari

  • November 8, 2005 at 4:39 am

    #602761

    Try this initially (quick and dirty solution). It will work for the data you have (noted typo on chidambaram/chidamabram on your INSERTs), however not if you had another level of management (i.e. if prabhu had someone reporting to them, that person would not be returned).

    SELECT emp_name

    FROM emp

    WHERE mgr_name = 'manmohan'

    UNION ALL

    SELECT emp_name

    FROM emp

    WHERE mgr_name IN (SELECT emp_name FROM emp WHERE mgr_name = 'manmohan')

  • November 8, 2005 at 6:13 pm

    #602935

    Hi Paul,

    Thanks for your reply, as u said your query will work only for one level.  Though it works for the sample data, it'll fail if there is another level of subordinate records.

    Thks for your time on this.

    Hari

     

     

    Kindest Regards,

    Hari

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply