CODEWITHSUNDEEP
sqlsqlite
Alexander Kochetov
Alexander Kochetov

Reputation: 73

How to select a group by members list

This is a SQL question.

Conditions: 

1. We have a list (table) of users  
2. Each user can belong to one or more Groups

So, there are three tables like these:

Users [userId, userName]
Groups [groupId, groupName]
UserGroups [userId, groupId]

The goal is to determine a group by exact list of its members.

For example, we have a query to get a group (if any) for two specific users. Our goal is to determine (SELECT) this one specific group with only those two members in it.

The obvious brute solution is to calculate and store a hash for each group's list of members, then calculate a hash for users list in query, and compare them. But it's too complicated, since requires a recalculation of all hashes when user is deleted from DB, or a new user is added to some group.

Maybe there are some comparison clauses I don't know in SQL?

Update.

Here is the solution I've made, basing on the answers:

WITH _userIds AS (SELECT id FROM users WHERE name IN ('user1', 'user2', 'user3')),
_groupIds AS (
    SELECT groupId FROM userGroups WHERE userId IN _userIds
    GROUP BY groupId
    HAVING COUNT(*) = (SELECT COUNT(*) FROM _userIds))
SELECT groupId FROM userGroups WHERE groupId IN _groupIds
GROUP BY groupId
HAVING COUNT(*) = (SELECT COUNT(*) FROM _userIds);

It's based mostly on the accepted answer and does the same things.

Upvotes: 2

Views: 1143

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

If you have the member to look for in a table is as simple as try to join them with the userGroups table and count how many match you get.

SELECT U.groupId
FROM UserGroups U
LEFT JOIN searchUsers S
  ON U.userId = S.userId
GROUP BY U.groupId
HAVING COUNT(*) = COUNT(S.userId)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270061

One method is:

select groupid
from usergroups
group by groupid
having sum(case when userid not in ( <members you care about> ) then 1 else 0 end) = 0 and
       count(*) = <# members you care about>;

If you don't want to count members explicitly, you can use a subquery:

with u as (
      select 1 as userid union all
      select . . .
     )
select groupid
from usergroups ug join
     u
     on ug.userid = u.userid
group by groupid
having count(*) = (select count(*) from u);

Upvotes: 0

Caius Jard
Caius Jard

Reputation: 74625

What about selecting all the members with an IN and counting?

SELECT groupID
FROM UserGroups
WHERE groupID IN(
  SELECT groupID
  FROM UserGroups
  WHERE userid IN (<list of all userids here>)
  GROUP BY groupID
  HAVING COUNT(*) = <count of userid list here>
) GROUP BY groupID
HAVING COUNT(*) = <count of userid list here>

Why do the count and group by twice? Well..

The inner one should select all groups that have all the listed users.. I.e. if you have 5 users in your IN list, and you have a group that only contains 4 of the 5, it is eliminated at this step.. But what about groups that have 6 members, 5 of whom are in the list? That's what the outer group by is for; the inner has prepared a "list of groups that definitely have these 5 members in, but maybe some additional members" and then the outer grouping collects together groups what ONLY have 5 members, which must be the 5 members in the IN list. If a group has 6 members it is eliminated

Note; I don't use or run SQLite, this is just using generic ansi SQL. I checked that SQLite can do HAVING - seems it can; if you get any errors or functionality problems with this query let me know via comment

Here's an alternative way of doing the same thing, using a JOIN instead of IN (long lists of IN can lead to performance problems in some DBs):

SELECT
  groupID
FROM
  UserGroups ug
  INNER JOIN
  (SELECT groupID countof FROM UserGroups GROUP BY groupID HAVING COUNT(*) = <list length>) countedGroups
  ON ug.groupID = countedGroups.GroupID
WHERE
  ug.UserID IN (<list of user ids>) 
GROUP BY groupID
HAVING COUNT(*) = <list length>

Upvotes: 2

Related Questions