I have the following three tables:

create table users(id int primary key, name varchar);
create table groups(id int primary key, name varchar);
create table user_groups(uid int references users(id), gid int references groups(id));  

Following are the records in the respective tables:

insert into users values (1, 'John'), (2, 'Bob'), (3, 'Maria'), (4, 'Jin');

insert into groups values (1, 'Persons - A'), (2, 'Persons - B'), 
(3, 'Persons - C'), (4, 'Persons - D'), (5, 'Pay - A'), (6, 'Pay - B'), (7, 'Pay - C');

insert into user_groups values (1, 1), (1, 2), (2, 1), (2, 4), (2, 6), (2, 7),
(3, 1), (3, 7), (4, 1), (4, 3);

Note: There are two types of groups, Persons and Pay

Now I want to find those users who have access to both types of groups (Persons and Pay), below is the desired output:

id        name       groups_user_has_access_to
2         Bob        Persons - A, Persons - D, Pay - B, Pay - C
3         Maria      Persons - A, Pay - C

The result should have only those users with their groups that have access to both types of groups.

Sorry for the bad English and appreciate your help in advance.


Here's my sample query for your question, the query could be slow depending on your table size

SELECT t.uid, u.name, t.groups_user_has_access_to
  -- Each of GID, finding its big group (PERSON, PAY) --> called: group_type. 
  -- Using distinct to remove duplicated element in group_type 
  SELECT ug.uid, 
    array_agg (distinct 
      case when g.name like 'Person%' then 'Person' 
           when g.name like 'Pay%' then 'Pay' end) as group_type, 
      array_agg(g.name) as groups_user_has_access_to
  FROM user_groups ug 
  JOIN groups g on ug.gid = g.id 
  GROUP BY ug.uid
JOIN users u on t.uid = u.id 
WHERE array_length(t.group_type, 1) > 1 -- Reject any UID which belong to 1 group.
ORDER BY t.uid; 

I would suggest you to read the array functions in the query above.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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