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

Question

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!

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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);

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

Sign up