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 wrote:
> 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,

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.
jstucklex@attglobal.net
==================

On Feb 19, 3:24 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> ram.infist...@gmail.com wrote:
> > 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,
>
> 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.
> jstuck...@attglobal.net
> ==================

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.