complex nested query

[impulse24]

Hi,

I have a table with:

employeenum|managernum|managerlevel

A single employee reports to one manager, and then there is chain. I am trying to figure out the top level managernum for an employee, being that the manager level is corporateitmanager.

Example:

EmployeeNum|Managernum|managerlevel
0000123 1111123 ITManager
1111123 2222123 RegionalITManager
2222123 3333123 CorpITManager


So the end result I am looking for is that the employeenum 0000123 CorpITManager is 3333123.

There are multiple RegionalITMAnagers, and multiple CorpITManager's. I am trying to get results that show the employeenum, and the managernum for the corpitmanager for that employee, so I can determine the number of employees reporting to the different corpitmanagers, and therenames.

I can't figure out the statement to use,it seems really complex. Can someone please help me. Thanks

 

[NovemberRain]

impluse24,

Here is a recursive sp that works with the specs you gave. Of course, fix the variable names to your implementation.

Cheers

NovemberRain

CREATE PROCEDURE dbo.GetCorpITManager
(
@EmpNum CHAR(7)
)
AS

-- DECLARE VARIABLES
DECLARE @ManagerNum CHAR(7)

DECLARE A CURSOR LOCAL READ_ONLY FOR
SELECT ManagerNum
FROM testdb.dbo.Emp AS T
WHERE T.EmployeeNum=@EmpNum
OPEN A
FETCH NEXT FROM A INTO @ManagerNum

WHILE (@@FETCH_STATUS = 0)
BEGIN
IF ((SELECT ManagerLevel FROM testdb.dbo.Emp AS T WHERE T.ManagerNum=@ManagerNum) = 'CorporateITManager')
SELECT @ManagerNum
ELSE
EXECUTE testdb.dbo.GetCorpITManager @ManagerNum

FETCH NEXT FROM A INTO @ManagerNum
END
CLOSE A
DEALLOCATE A
GO

 

[mbaza3]

This should work:

SELECT i.EmployeeNum, c.Managernum AS CorpITManager
FROM employees i
INNER JOIN (SELECT * from employees) r
ON i.Managernum = r.EmployeeNum
INNER JOIN (SELECT * from employees) c
ON r.Managernum = c.EmployeeNum