I have a hierarchical query (which works) in a stored procedure as follows which represents people within a hierarchical organization:ALTER PROCEDURE [dbo].[udp_GetHeirarchyForUserID] @UserID varchar(10)ASBEGIN WITH Tree ([LoginID] ,[GroupID] ,[SupervisorID] ,Level) AS(-- Anchor member definition SELECT [LoginID] ,[GroupID] ,[SupervisorID] ,0 as Level FROM [tReporting_Structure] where SupervisorID = @UserID UNION ALL-- Recursive member definition SELECT rs.[LoginID] ,rs.[GroupID] ,rs.[SupervisorID] ,Level + 1 FROM [tReporting_Structure] as rs INNER JOIN Tree AS d ON rs.SupervisorID = d.LoginID)-- Statement that executes the CTESELECT [LoginID] ,un.LastName ,un.FirstName ,d.[GroupID] ,[SupervisorID] ,g.GroupName ,LevelFROM Tree d left join tReporting_Group g on d.GroupID = g.GroupID left join tUserNames un on d.LoginID = un.UserID;END
I need to be able to join the output of this procedure with another table containing UserIDs as one of their elements... such that I can pull records for a given Supervisor (which would give me the records for everyone who works underneath them).How can I join to the result of a stored procedure? Am I even going about this the right way?Any help would be appreciated! Thanks!