Making a recursive sql call using MS SQL Server 2005

pborregg
pborregg used Ask the Experts™
on
I have a simple yet difficult recursive sql I'd like to make.

Here's the simple version:

select supervisor from hr_employee_info where eid = "xxx";  <-- EID is a given through the session variable.  I'm basically going to use the same query to get the supervisor's first name and last name for the EID equal to XXX.

Now from the same table, I want to get the supervisor first_name and last_name.

Here's the columns:

first_name
last_name
eid (Employee ID)
supervisor

So basically, using XXX as my parameter, I need to recursively call the same table to get the supervisors first_name and last_name. The XXX is the initials of the employee. Each employee has a supervisor assigned to them.  So basically employee XXX has supervisor SSJ and SSJ's first name is Sam and the last name is Jones.  You follow?

Thanks... Again, this needs to be SQL!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012

Commented:
If you just have one level of hierarchy then it is as simple as using a self join.  As in:

SELECT  *
FROM    hr_employee_info e
        INNER JOIN hr_employee_info s ON e.eid = s.eid
WHERE   e.eid = 'xxx'
Top Expert 2012
Commented:
Let's try that again:

SELECT  *
FROM    hr_employee_info e
        INNER JOIN hr_employee_info s ON e.supervisor = s.eid
WHERE   e.eid = 'xxx'

Author

Commented:
This is more what I needed but you put me in the right direction... Thank you.

SELECT  s.first_name, s.last_name, e.supervisor
FROM    hr_employee_info e
        INNER JOIN hr_employee_info s ON e.supervisor = s.eid
WHERE   e.eid = "XXX"

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial