Returning table based on comparing list of values in one View to a list in another

Question

I have two SQL Views that expose data relating to Entity / Classification pairings and another that exposes User Classification pairings.

In order for a user to have permission to access an entity the user must have ALL the categories assigned to that entity. So:

EntityID      ClassificationID
1             1
1             2
2             1
2             2
2             3


UserID        ClassificationID
100           1
100           2
100           4
101           1
101           2
101           3 

In the above scenario User 100 has access to entity ID 1, but user 101 has access to both 1 and 2

I want to be able to return this data in a table like this, essentially a complete list of entitles and users that have access to them:

UserID      EntityID
100         1
101         1
101         2     

What is the best and most performant way of achieving this. I am using SQL Server 2019

Answer 1

This is a relational division problem. I would recommend a join to relate the users and entities, then aggregation, and filtering with a having clause to retain only "complete" groups.

Assuming that the tables are called entities and users:

select u.userid, e.entityid
from entities e
inner join users u on u.classificationid = e.classificationid
group by u.userid, e.entityid
having count(*) = (select count(*) from entities e1 where e1.entityid = e.entityid)

Demo on DB Fiddle:

userid | entityid
-----: | -------:
   100 |        1
   101 |        1
   101 |        2