Louis Capece
asked on
SQL for recursive tree query
Need to produce a query of all employees that "Report To" a given magager. Multiple "Report-To" levels exist.
Assume this table:
EMPLOYEE_ID INT
EMPLOYEE_NAME VARCHAR
REPORTS_TO_ID INT
The query in query may have any number of levels in the heirarchy. I would like to create a function or SP that accepts a single ID and returns all unique Employee ID's who directy OR indirectly report to that person. Thanks for your time in looking at my problem.
Assume this table:
EMPLOYEE_ID INT
EMPLOYEE_NAME VARCHAR
REPORTS_TO_ID INT
The query in query may have any number of levels in the heirarchy. I would like to create a function or SP that accepts a single ID and returns all unique Employee ID's who directy OR indirectly report to that person. Thanks for your time in looking at my problem.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
perhaps I was hasty with my response, you may be able to join back to that table this time looking for the employee id (that is the reports_to id from the initial select) and finding who they then report to and so on.
Seems messy though.
Seems messy though.
angel is correct, I read this in the 2000 forum and didn't notice it had been cross posted to 2005.
See below where @ID represents passed in ID parameter. You can make more detailed by doing a LEFT JOIN to EMPLOYEES table again to link REPORTS_TO_ID to EMPLOYEE record in order to select EMPLOYEE_NAME and SUPERVISOR_NAME to see direct reports versus sub levels. Can do that simply by adding column REPORTS_TO_ID in select statement then sort on that in resultset.
SELECT EMPLOYEE_NAME
FROM EMPLOYEES
WHERE REPORTS_TO_ID = @ID
OR REPORTS_TO_ID IN (SELECT DISTINCT EMPLOYEE_ID FROM EMPLOYEES WHERE REPORTS_TO_ID = @ID)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Should be distinct, but you can always select distinct:
SELECT DISTINCT EMPLOYEE_ID, EMPLOYEE_NAME, REPORTS_TO_ID
FROM dbo.fn_GetReports(5)
For example if manager ID is 5.
This...
-- grab the next record
SELECT @currentID = MIN(id)
FROM EMPLOYEES WHERE REPORT_TO_ID = @managerID
AND EMPLOYEE_ID > @currentID
Should be...
-- grab the next record
SELECT @currentID = MIN(EMPLOYEE_ID)
FROM EMPLOYEES WHERE REPORT_TO_ID = @managerID
AND EMPLOYEE_ID > @currentID
Copied and pasted from another function I created as an example and forgot to change. If you want the manager himself/herself to be included in the query, you simply move the insert current record block above the while loop and change from @currentID to @managerID that way it will include manager then explode downward which at each level will include that employee before exploding downward.
SELECT DISTINCT EMPLOYEE_ID, EMPLOYEE_NAME, REPORTS_TO_ID
FROM dbo.fn_GetReports(5)
For example if manager ID is 5.
This...
-- grab the next record
SELECT @currentID = MIN(id)
FROM EMPLOYEES WHERE REPORT_TO_ID = @managerID
AND EMPLOYEE_ID > @currentID
Should be...
-- grab the next record
SELECT @currentID = MIN(EMPLOYEE_ID)
FROM EMPLOYEES WHERE REPORT_TO_ID = @managerID
AND EMPLOYEE_ID > @currentID
Copied and pasted from another function I created as an example and forgot to change. If you want the manager himself/herself to be included in the query, you simply move the insert current record block above the while loop and change from @currentID to @managerID that way it will include manager then explode downward which at each level will include that employee before exploding downward.
The direct is easy....
create procedure WhoWorksForMe
@Reports_to int
as
select employee_id
from thisTable
where Reports_to_id = @Reports_to
but that's sort of where the trail ends (from the schema you've shown), how then would you see the rest of the chain of command unless you had some extra columns/table that show who the reports to people report to themselves?