CODEWITHSUNDEEP
sqlpostgresql
orangeBall
orangeBall

Reputation: 123

Complex query in a many to many relationship

I have two tables with a Many to Many relationship:

TABLE USER(id, name, ...)
TABLE GROUP(id, name, ...)
TABLE USER_GROUP(user_id, group_id)

Given a list of usernames, I want my backend to send an SQL query to the database.

This query must select all groups (from table GROUP) which contain at least all users (from table USER) specified in the input list.

Please note that the input list contains the usernames and not the user ids.

Requirements:

Expected results:

Input list (usernames) Expected result (groups)
John, Celia Group A, Group C
John, Jack, Celia Group A
Jack Group A, Group B

Data :

Table USER

+------+-------------------+
| ID   | NAME              |
+------+-------------------+
| 1    | John              |
| 2    | Jack              |
| 3    | Celia             |
| 4    | Alice             |
+------+-------------------+

Table GROUP

+------+-------------------+
| ID   | NAME              |
+------+-------------------+
| 1    | Group A           |
| 3    | Group B           |
| 4    | Group C           |
| 5    | Group D           |
+------+-------------------+

Table USER_GROUP

+-----------+-------------------+
| GROUP_ID  | USER_ID           |
+-----------+-------------------+
| 1         | 1                 |
| 1         | 2                 |
| 1         | 3                 |
| 2         | 1                 |
| 2         | 2                 |
| 3         | 1                 |
| 3         | 3                 |
| 3         | 4                 |
+-----------+-------------------+

EDIT:

I use PostgreSQL as my RDBMS

Upvotes: 2

Views: 331

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Let me assume that you are passing the values in as an array. You can use:

select g.name
from groups g join
     user_groups ug
     on ug.group_id = g.id join
     users u
     on ug.user_id = u.id
where u.name = any (:input_list)
group by g.name
having count(*) = cardinality(:input_list);

Here is a db<>fiddle, with the data fixed to match the results you have in the question.

Upvotes: 1

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

CREATE TABLE Users (User_ID INT, Name VARCHAR(255));
CREATE TABLE Groups (Group_ID INT, Name VARCHAR(255));
CREATE TABLE User_Group (User_ID INT , Group_ID INT);
INSERT INTO Users (User_ID,Name) VALUES (1,'John'),(3,'Celia');
INSERT INTO Groups (Group_ID,Name) VALUES(1,'Group A'),(4,'Group C');
INSERT INTO User_Group(User_ID, Group_ID) VALUES(1,1),(3,4);

SELECT  string_agg(U.Name::text, ',') AS UserNames
,string_agg(G.Name::text, ',') AS GroupNames FROM User_Group AS UG
INNER JOIN Users AS U ON U.User_ID=UG.User_ID
INNER JOIN Groups AS G ON G.Group_ID=UG.Group_ID
WHERE U.Name IN ('John','Celia')

usernames  | groupnames     
:--------- | :--------------
John,Celia | Group A,Group C

db<>fiddle here

Upvotes: 1

Related Questions