• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Need a solution to return a nested list of users

I have a simple table of users. I have a unqiueidentifier (GUID) as the key field. I also have afield that shows the user ID for that user's direct report manager.

I'm trying to write a stored procedure that returns a list of GUIDs that a particular user oversees.

The first stage of course was easy:

SELECT UserID FROM Users WHERE Manager = @ThisUserID

Here's the catch...that's not the full list. That's just the list of direct reports. I also need the output to show the list of users that each of THOSE users oversees...and the list of users that eeach of THOSE users oversees....and so on and so on.

And of course, as I'm typing this, I realize that I could find myself in a recursive loop, but I supose each of the layered parts could have the qualfier AND NOT IN (CurrentListOfUsers).
0
CCongdon
Asked:
CCongdon
  • 3
  • 2
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
"Recursive" is the key word there.  You can accomplish this by a recursive common table expression (CTE) within SQL Server 2005 as one approach.  
The CTE below is setup with some extra information like level and manager, but showed final select per your requirements which is a distinct list of UserIDs.

with cte(employee, lvl, manager)
as
(
-- get the first level
select UserID, 1, Manager 
from Users 
where Manager = @ThisUserID

union all -- allows recursion

-- get the direct reports of direct reports
select u.UserID, m.lvl+1, u.Manager
from Users u
inner join cte m on u.Manager = m.employee
)
select distinct employee
from cte
;

Open in new window

0
 
CCongdonAuthor Commented:
OK, I've never done a CTE before. But I think I got it figured out. One issue I see is that I'm getting two resultsets out of this....or I didn't set it up right.
The second isssue is that it handles recursion, but not recursive loops. An example:
User1 - Manager set to User 3
User 2 - Manager set to User 1
User 3 - Manager set to User 2
User 4 - Manager set to User 3
If you do this CTE for User2, User3, or User 4, you get a recursion loop exceeded error.
0
 
CCongdonAuthor Commented:
Never mind the 'two' resultsets problem. As I said 'or I set it up wrong'. I misunderstood the error message. I had forgotten to terminate the previous line with a semi-colon.
0
 
Kevin CrossChief Technology OfficerCommented:
Yes, it should be fine as in normal heirarchies you don't get a top level manager who reports to someone down the management chain; however, to account for that you could do this:

with cte(employee, lvl, manager)
as
(
-- get the first level
select UserID, 1, Manager
from Users
where Manager = @ThisUserID

union all -- allows recursion

-- get the direct reports of direct reports
select u.UserID, m.lvl+1, u.Manager
from Users u
inner join cte m on u.Manager = m.employee
where u.UserID <> @ThisUserID /* stop endless loop */
)
select distinct employee
from cte
;
0
 
CCongdonAuthor Commented:
Yes, I know you don't normally have a lower level reporting to a higher level, but you never know what some user might try to put into the system because they feel like it.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now