CODEWITHSUNDEEP
sqlsql-serverssis
William Xu
William Xu

Reputation: 261

How to check if a set is a subset of another set

Here's my SQL problem. I have 2 tables:
User Table: 

[UserID]   [Access]  
Abe        A
Abe        B
Brad       C
Cathy      A

Role Lookup Table:

[Role]      [Access]
Admin       A
Admin       B
ReadOnly    C

I need to check if a user has all the Accesses of a Role, and end up a table like this:

[UserID]   [Role}
Abe        Admin
Brad       ReadOnly
Cathy      None

In addition, a user can have multiple Roles, as long as it has all the Accesses of these Roles.

Can anyone point me the direction? Showing me the logic or basic method would be helpful enough. I'd need to code this in T-SQL. Thanks!

Upvotes: 2

Views: 5227

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can get all roles that user has access to as:

select u.userid, r.role
from users u join
     roles r
     on u.access = r.access
group by u.userid, r.role
having count(*) = (select count(*) from roles r2 where r2.role = r.role);

Upvotes: 3

Related Questions