Show All Users Who Have Multiple Managers

Question

I have a database that holds user information. I need a way to find out what users have multiple managers. Below is a subset of data to illustrate the data structure.

Using MS SQL Server 2016 what would be a query to show me all userid and managerid where for each user the count of managerid > 1?

Declare @@Test Table
(
    userid varchar(100),
    mngrid varchar(100)
)


Insert Into @@Test Values ('abc-413', '619232'), ('abc-413', '39021'), ('xyz-999', '39021')

Select * FROM @@Test

Answer 1

Hi @Johnathan Simpson ,

Please refer to:

Declare @@Test Table  
(  
    userid varchar(100),  
    mngrid varchar(100)  
)  
Insert Into @@Test Values ('abc-413', '619232'), ('abc-413', '39021'), ('xyz-999', '39021')  
  
select *   
from   
  (select *,count(mngrid) over(partition by userid) num   
  from @@Test)t  
where num>1  

Output:

userid  mngrid  num  
abc-413 619232 2  
abc-413 39021 2  

Or:

Declare @@Test Table  
(  
    userid varchar(100),  
    mngrid varchar(100)  
)  
Insert Into @@Test Values ('abc-413', '619232'), ('abc-413', '39021'), ('xyz-999', '39021')  
  
;with cte  
as(select *,count(mngrid) over(partition by userid) num   
  from @@Test)  
select * from cte  
where num>1  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

There are several ways to accomplish this query. But, I believe your teacher is looking for a HAVING clause:

Select userid 
FROM @@Test
GROUP BY userid
HAVING COUNT(DISTINCT mngrid) > 1