Postgresql – How to find out those users which have two different access right in postgresql


Thanks for your support and help,
Dear the scenario is like this i have 3 tables

1: users
2: users_groups
3: users_groups_rel 

users data

id      name
1       zubair
2       ali
3       bob
4       john

users_groups data

   id    name
    1   report
    2   finance
    3   personnel

users_groups_rel data; it is associated table of users and users_groups

 uid    gid
    1   1
    1   2
    1   3
    2   3
    3   2
    4   2
    4   1

Now i want to have those users which have access just to (Report +
finance) When i am running this bellow query the result is incorrect,
it shows those users which have id of (1 , 4) but i don’t need this i
need that users which mentioned the exact access right for them.

i need those users which have access to these access right
(report and finance)

SELECT r.uid,
   FROM users_groups_rel r left join users u on
   GROUP BY r.uid,
   HAVING count(DISTINCT gid in (select id from users_groups where id=2)) = (SELECT count(DISTINCT gid in (select id from users_groups where id=1))

Best Answer

You can aggregate and check if the distinct count per user is equal to the count of distinct groups of the whole table.

Assuming that the user is identified by a column user_id and the group by a column group_name.

SELECT user_id
       FROM users_group
       GROUP BY user_id
       HAVING count(DISTINCT group_name) = (SELECT count(DISTINCT group_name)
                                                   FROM user_group);