Dear all,

I have a scenario, where I have a single table with all my users listed in them. Each user also has a field indicating the user they report to.

UserID      UserDisplayName                                              UserReportsTo
----------- ------------------------------------------------------------ -------------
8           Anthony Karimi                                               3
9           naheed kassam                                                8
1           Administrator                                                1
3           Ramakrishna                                                  2
2           SB                                                           2

(5 row(s) affected)

In my scenario, i need when the user selected is say userid = 3, then the table displays userid = 3; userid = 8 who reports to userid = 3; and userid = 9 who reports to userid = 8 as shown below

UserID      UserDisplayName           UserReportsTo
----------- ------------------------- -------------
8           Anthony Karimi            3
9           naheed kassam             8
3           Ramakrishna               2

I would really appreciate if someone can assist me achieve this for my CRM project.


5 Days
Discussion Span
Last Post by naheedkassam

Trying to get my head around this. You want the actual user id=3 and every user that has the same boss?


@pty thanks for the example, i am trying to achieve the same but using 1 single table.


Thanks guys for the tips. I solved it using below query.

WITH ReportsCTE as
(select UserID,UserDisplayName,UserReportsTo from CRM_User_Master where UserDisplayName in(select UserDisplayName from CRM_User_Master where UserID = 1)
union ALL
select a.UserID,a.UserDisplayName,a.UserReportsTo
from CRM_User_Master a
inner join ReportsCTE s on a.UserReportsTo = s.userid
select * from reportscte

Votes + Comments
Looks good!
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.