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.