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,
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.