I am trying to write a query to determine who, in my company, has roles that I specify, and no others.
The tables are User, UserRole, and Role. An (incorrect) example is below and I have tried a few different ways like this, but they all seem to return a user when they just contain the roles.
select U.Username from User U
join UserRole UR on U.UserID = UR.UserID
join Role R on UR.RoleID = R.RoleID
where R.RoleName in ('Role1', 'Role2', 'Role3')
Example User table
| User ID | UserName |
|---|---|
| 1 | Joe |
| 2 | Bob |
Example UserRole Table
| UserID | RoleID |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
Example Role Table
| RoleID | RoleName |
|---|---|
| 1 | Admin |
| 2 | SysAdmin |
| 3 | Manager |
For example, I want to query for everyone that only has the SysAdmin, and manager roles. Although Joe has those roles I don't want him to be included in the result.
I feel like there is something simple that I am missing. However, after doing research online, I am unable to find a similar scenario.
CodePudding user response：
If I understood your requirements:
select U.Username
from User U
join UserRole UR on U.UserID = UR.UserID
join Role R on UR.RoleID = R.RoleID
where R.RoleName in ('Role1', 'Role2', 'Role3')
GROUP BY U.Username
HAVING COUNT(R.RoleName)=3
The above is untested but should give you enough hints to solve your problem