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.

Regards,
Naheed

Member Avatar

diafol

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

That sounds like what he's after. With more than one level you'd need to use a window query to do this. Here's an example I wrote many years ago.

@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

commented: Looks good! +9