Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL for layers of managers
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
18 Posts

Posted - 11/29/2012 :  13:00:46  Show Profile  Reply with Quote
I have a table of employees with fields of employeeid, managerid. Each user has a manager and each manager has a manager to a number of levels, so:
employeeid, managerid with content:
1, 5
2, 5
3, 7
4, 8,
5, 10
6, 5
etc and going on for an unknown number of manager levels. I need to get out a Manager of any level who can have employees/managers beneath him who if managers, have their own employees under them. All levels need to be returned.
Can anyone help with the best way to do this?
Any help gratefully rec'd.

Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/29/2012 :  13:16:26  Show Profile  Reply with Quote
A recursive CTE would be the best way to do this. The MSDN page has documentation and an example which is very similar to the problem you are trying to solve:

Take a look at the example there, and if need help tweaking it for your purposes, please reply?
Go to Top of Page

Starting Member

United Kingdom
18 Posts

Posted - 11/29/2012 :  17:25:12  Show Profile  Reply with Quote
Hi, Many thanks for the pointer. I found the examples there hard work to follow and found the ones here are a more basic and easier to follow set of details: I will try the below on the db at work and confirm if it does the job:
WITH EmployeeHierarchy (EmployeeID, LastName, FirstName, ManagerID, HierarchyLevel) AS
-- Base case
1 as HierarchyLevel
FROM myEmployees
WHERE employeeid=285--ManagerID IS NULL (Replaced to enter ID of Manager that wants to run the report.)


-- Recursive step
eh.HierarchyLevel + 1 AS HierarchyLevel
FROM myEmployees e
INNER JOIN EmployeeHierarchy eh ON
e.ManagerID = eh.EmployeeID

FROM EmployeeHierarchy
ORDER BY HierarchyLevel, LastName, FirstName
OPTION (Maxrecursion 10000)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000