POSTGRESQL - Find a row with a specific set of join table data

Question

I have Users in Groups. I am trying to find which Group contains ONLY a specific set of Users. For instance Bob is in the group [Bob + John], but also in the group [Bob + John + Steve], and I would like to match the first one.

I use a join table groups_users to link Users to Groups.

I am having a hard time coming up with a query that will use the join table to match the users to the group, but also using that join table to exclude groups (groups that do not have the exact set of user searched).

Here is a fiddle with some data.

Schema (PostgreSQL v13 (Beta))

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  username VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE groups (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE groups_users (
  group_id INT NOT NULL,
  user_id INT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_group
  FOREIGN KEY(group_id)
  REFERENCES groups(id),
  CONSTRAINT fk_user
  FOREIGN KEY(user_id)
  REFERENCES users(id)
);

INSERT INTO users (username)
VALUES ('bob'), ('john'), ('steve');

INSERT INTO groups DEFAULT VALUES;
INSERT INTO groups DEFAULT VALUES;
INSERT INTO groups DEFAULT VALUES;

INSERT INTO groups_users (group_id, user_id)
VALUES (1, 1),
    (1, 2),
    (2, 2),
    (2, 3),
    (3, 1),
    (3, 2),
    (3, 3);

Query #1

SELECT * FROM groups_users
WHERE groups_users.user_id IN (1, 2);

| group_id | user_id | created_at               |
| -------- | ------- | ------------------------ |
| 1        | 1       | 2020-11-22T16:12:35.796Z |
| 1        | 2       | 2020-11-22T16:12:35.796Z |
| 2        | 2       | 2020-11-22T16:12:35.796Z |
| 3        | 1       | 2020-11-22T16:12:35.796Z |
| 3        | 2       | 2020-11-22T16:12:35.796Z |

We see that we match groups 1, 2 and 3, but we only want to match 1, and I don't know how to go about querying this.

Thank you for your help.

user_3984221 · Answer

You can group your groups and aggregate the user_ids into arrays. Than can compare these aggregations with created user_id arrays:

demo:db<>fiddle

SELECT
    group_id
FROM
    groups_users
GROUP BY group_id
HAVING ARRAY_AGG(user_id) = ARRAY[1,2]

user_5120073 · Answer

This is gross and I apologize, but what this query does is gets a count of the row results for groups with your filter applied and then compares it to the total members of the group and only includes groups which only include those members.

SELECT t1.group_id FROM 
(
SELECT  group_id, COUNT(group_id) AS Instances FROM groups_users
WHERE groups_users.user_id IN (1, 2)
GROUP BY group_id
) T1
INNER JOIN 
(
SELECT  group_id, COUNT(group_id) AS Instances FROM groups_users
GROUP BY group_id
) T2
ON T1.group_id = T2.group_id and t1.Instances  = t2.Instances

POSTGRESQL - Find a row with a specific set of join table data

2 Answers