CODEWITHSUNDEEP
sqlpostgresqlmany-to-many
apchester
apchester

Reputation: 1144

SQL query to filter many-to-many

I have users, groups and users_groups tables set up as below:

users
  id int

groups
  id int

users_groups
  user_id int
  group_id int

Example Data

+-------+--------+
|user_id|group_id|
+-------+--------+
|  1    |    1   |
|  1    |    2   |
|  1    |    3   |
|  2    |    1   |
|  2    |    2   |
|  3    |    2   |
|  3    |    3   |
+-------+--------+

Is there a way to select all users who belong to a subset of groups which has an unknown size? This is essentially filtering based on the group ids.

For example a query that returns user ids for members of groups 1 and 2 would return a user ids of [1,2], but then when asked for members of groups 1,2 and 3 returns a user id of [1]

Upvotes: 0

Views: 446

Answers (2)

roman
roman

Reputation: 117345

basically you can do this by aggregating and counting number of entries. You can do this by simple query, like juergen_d did, or use function like this:

create function fn_users_groups(_groups int[])
returns table(user_id int)
as
$$
    select user_id
    from users_groups
    where group_id = any(_groups)
    group by user_id
    having count(*) = array_length(_groups, 1)
$$
language sql;

=> sql fiddle demo

Upvotes: 1

juergen d
juergen d

Reputation: 204756

If you want only users being in all 3 groups use

select user_id
from users_groups ug
where group_id in (1,2,3)
group by user_id
having count(distinct group_id) = 3

Upvotes: 1

Related Questions