William Xu William Xu - 4 years ago 93
SQL Question

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!

Answer Source

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);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download