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.

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

SELECT r.uid,u.name
   FROM users_groups_rel r left join users u on u.id=r.uid
   GROUP BY r.uid,u.name
   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))

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);
  • Dear stiky please see the scenario which i edit and need your help. – Zubair Jan 13 at 10:11

Here's one attempt. Note that you must have unique constraints on group.name for this to work:

select u.id, u.name 
from users u 
join user_groups_rel r 
    on u.id = r.uid 
join user_groups g 
    on r.gid = g.id 
group by u.id, u.name 
having count(distinct case when g.name in ('report','finance')
                           then g.id 
            ) = 2 
   and count(distinct g.id) = 2

In short, which users are members of exactly 2 groups and also members of exactly 2 groups among report and finance.

If you are only interested in the id of the user, you can remove the user table, and adjust the query accordingly.

Some notes on naming conventions, don't use vague identifiers like id. Use for example user_id, and stick with that in related tables:

( user_id int not null primary key
, user_name varchar(20) not null

( group_id int not null primary key
, group_name varchar(20) not null unique

CREATE TABLE user_groups
( user_id int not null references users (user_id)
, group_id int not null references groups (group_id)
,    primary key (user_id, group_id)

You could use array_agg and compare it with the desired conditions:

    uid, array_agg(gid ORDER BY uid, gid) grp
uid | grp    
--: | :------
  1 | {1,2,3}
  2 | {3}    
  3 | {2}    
  4 | {1,2}    -- reports and finances only
HAVING array_agg(gid ORDER BY uid, gid) = ARRAY[1, 2];
| uid |
| --: |
|   4 |

db<>fiddle here


If you want to use group's name instead of id's you can use next query:

    users_groups_rel ugr
    users_groups ug
    ON ug.id = gid
    array_agg(ug.name::text ORDER BY ugr.uid, ugr.gid) = ARRAY['report', 'finance'];

db<>fiddle here

  • Thanks a lot for your corporation Mr.McNets,the query which you added that is completely fine, but i have another question regarding this (i have 100+ access right now i want to use the group names instead of group id how should i used your query. – Zubair Jan 15 at 7:26
  • Dear when i run this query into my real DB i faced to this (ERROR: operator does not exist: character varying[] = text[] LINE 11: ... array_agg(rg.name ORDER BY rugr.uid, rugr.gid) = ARRAY['A... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. ) – Zubair Jan 15 at 9:54
  • What data type is name ? – McNets Jan 15 at 10:33
  • The data type is character varying – Zubair Jan 15 at 10:35
  • Then cast name as text name::text. dbfiddle.uk/… – McNets Jan 15 at 14:00

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.