Professional Web Applications Themes

How to write self referential sql query to fetch the bosses(direct and indirect) for a given employee? - MySQL

I have a task in hand to determine the bosses both direct and indirect for a given employee. Since even the boss is an employee, i have defined it as a simple self referential table. Table has 3 Columns employee_id // PK employee_name boss_id // FK - Pointing to the same table to the employee_id I have 5 rows now in the table: employee_id employee_name boss_id 1 John,Chambers NULL 2 Jacob, Philips 1 3 Tom, Hanks 2 4 Mathew,Con 3 5 Hilary,clinton 2 I need sql query which can return the direct and indirect bosses for given emploee_id in the ...

  1. #1

    Default How to write self referential sql query to fetch the bosses(direct and indirect) for a given employee?

    I have a task in hand to determine the bosses both direct and indirect
    for a given employee. Since even the boss is an employee, i have
    defined it as a simple self referential table.

    Table has 3 Columns
    employee_id // PK
    employee_name
    boss_id // FK - Pointing to the same table to the employee_id

    I have 5 rows now in the table:
    employee_id employee_name boss_id
    1 John,Chambers NULL
    2 Jacob, Philips 1
    3 Tom, Hanks 2
    4 Mathew,Con 3
    5 Hilary,clinton 2

    I need sql query which can return the direct and indirect bosses for
    given emploee_id in the order?
    For example:
    findBoss(5) should return :(in the order)
    Jacob,Philips
    John,Chambers

    findBoss(4) should return : (in the order)
    Tom, Hanks
    Jacob, Philips
    John Chambers

    How can I do that??
    Any help would be greatly appreciated!!

    Thanks
    Ram

    ram.infistech@gmail.com Guest

  2. #2

    Default Re: How to write self referential sql query to fetch the bosses(directand indirect) for a given employee?

    com wrote: 

    Ram,

    MySQL doesn't support recursive SQL, which is what you would need. You
    should be able to do it in a stored procedure, however.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: How to write self referential sql query to fetch the bosses(direct and indirect) for a given employee?

    On Feb 19, 3:24 pm, Jerry Stuckle <net> wrote: 






    >
    > Ram,
    >
    > MySQL doesn't support recursive SQL, which is what you would need. You
    > should be able to do it in a stored procedure, however.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    or just left join the table to itself enough times to be sure you're
    at the top of tree.

    not sure why you've got commas between first and last names though.

    strawberry Guest

Similar Threads

  1. Self-referential table - help with query
    By disney1981 in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 31st, 04:41 PM
  2. need tag for direct write to desktop
    By nick2net in forum Coldfusion - Advanced Techniques
    Replies: 3
    Last Post: August 19th, 01:21 AM
  3. write query in SQL Query yzer
    By John in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 5th, 08:55 PM
  4. How to write this query... please help!
    By Paul Dussault in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 7th, 02:45 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139