CODEWITHSUNDEEP
sql
Andrei Orlov
Andrei Orlov

Reputation: 908

Select subset of records within the same table

There is a table that presents a mapping for permissions and some objects, i.e. an object 'obj1' has 'perm1' and 'perm2' permissions, 'obj2' - 'perm1' and 'perm3'.

Permission_id Object_Id
  perm1      |   obj1
  perm2      |   obj1
  perm1      |   obj2
  perm3      |   obj2

The question is: how to get a subset of permissions which are applied to ALL objects? I.e. :

Permission_id 
    perm1

Upvotes: 0

Views: 66

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

The following uses counts and aggregations for this purpose:

select permission_id
from permissionobjects po
group by permission_id
having count(distinct object_id) = (select count(distinct object_id) from permissionobjects);

Upvotes: 1

Related Questions