Nesting SQL statements from a single table

[naheedkassam]

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

[diafol]

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

[pty]

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.

[naheedkassam]

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

[naheedkassam]

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