Sql-server – Query To Show All Users Who Have Multiple Managers


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

Best Answer

You just need to use the GROUP BY and the HAVING clause to filter the Table down to only users with multiple managers like so:

SELECT userid
GROUP BY userid
HAVING COUNT(mngrid) > 1

Also I don't think @@Test is valid syntax (though I'm unable to test right now) so my example just uses a regular Table, but you can replace it with your actual Table name.