Need a solution to return a nested list of users
Posted on 2009-12-22
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).