SQL: Selecting Collections Containing Exactly Specified Members

Question

I am sure that there is a right word for this that I don’t remember, but the problem is easy to describe: I have a group groupmembers, which is a simple relationship between groups and members:

id | groupid | memberid 1 | g1 | m1 2 | g1 | m2 3 | g2 | m1 4 | g2 | m2 5 | g2 | m3 

Two groups are described above: one with m1 and m2 and one with m1, m2 and m3. If I want to select groups that have members m1, m2 but no other members, how do I do this? The approaches I tried also return g2, since m1 and m2 are their subsets.

UPDATE : Wow, some great answers! Let me first clarify my question a bit - I want to be able to choose a group that exactly matches the given members m1 and m2. Therefore, it should not match if the group also contains more members than m1 and m2, and it should NOT match if the group contains fewer members m1 and m2.

Answer 1

from your phrase

I want to select groups that have members m1, m2, but no other members

try this, the idea is to count total record instances that match the where clause and clause, and that it is equal to the total number of records per group.

 SELECT groupid FROM table1 a WHERE memberid IN ('m1','m2') GROUP BY groupid HAVING COUNT(*) = ( SELECT COUNT(*) FROM table1 b WHERE b.groupid = a.groupid GROUP BY b.groupID ) 

SQLFiddle Demo

Answer 2

You are looking for an intersection between groups that have m1 and m2, and those groups that have exactly two members. SQL has a statement for this:

 select groupid from group_table where memberid in ('m1','m2') group by groupid having count(distinct memberid) = 2 intersect select groupid from group_table group by groupid having count(distinct memberid) = 2 

(If you are using Oracle, intersect is called minus )

Here is the SQLFiddle demo: http://sqlfiddle.com/#!12/df94d/1

Although I think John Woo's solution might be more efficient in terms of performance.

Answer 3

 -- sample table for discussion CREATE TABLE tbl (id int, groupid varchar(2), memberid varchar(2)); INSERT INTO tbl (id, groupid, memberid) VALUES (6, 'g4', 'm1'), (7, 'g4', 'm2'), (8, 'g6', 'm1'), (9, 'g6', 'm3'), (1, 'g1', 'm1'), (2, 'g1', 'm2'), (3, 'g2', 'm1'), (4, 'g2', 'm2'), (5, 'g2', 'm3') ; -- the query select a.groupid, b.groupid peer from (select groupid, count(*) member_count, min(memberid) x, max(memberid) y from tbl group by groupid) A join (select groupid, count(*) member_count, min(memberid) x, max(memberid) y from tbl group by groupid) B on a.groupid<b.groupid and a.member_count=b.member_count and ax=bx and ay=by join tbl A1 on A1.groupid = A.groupid join tbl B1 on B1.groupid = B.groupid and A1.memberid = B1.memberid group by A.groupid, b.groupid, A.member_count having count(1) = A.member_count; -- the result GROUPID PEER g1 g4 

The above method allows you to get groups specified with their peers in a very optimal way. It works well with large databases, decomposing groups into the number of members and takes a minimum and a maximum. Groups are quickly reduced using direct joins, and for the remaining matches only, the full table refers to joining back with the identifiers of groups A and B to determine if they are equivalent groups.

If you had three identical groups (101,103,104), they will be displayed as three separate lines (101,103), (101,104), (103,104) - since each pair forms a peering, so this query is best used if you already know one of the groups for which you want to find peers. This filter would fit in the first subquery.

Answer 4

 SELECT DISTINCT -- if (groupid, memberid) is unique -- no need for the DISTINCT a.groupid FROM tableX AS a JOIN tableX AS b ON b.groupid = a.groupid WHERE a.memberid = 'm1' AND b.memberid = 'm2' AND NOT EXISTS ( SELECT * FROM tableX AS t WHERE t.groupid = a.groupid AND t.memberid NOT IN ('m1', 'm2') ) ; 

Answer 5

there is a problem with this request

 SELECT groupid FROM table1 a WHERE memberid IN ('m1','m2') GROUP BY groupid HAVING COUNT(*) = ( SELECT COUNT(*) FROM table1 b WHERE b.groupid = a.groupid GROUP BY b.groupID ) 

It will correspond to groups with only m1 or only m2. To do this, we can add another account check

 SELECT groupid FROM table1 a WHERE memberid IN ('m1','m2') GROUP BY groupid HAVING COUNT(*) = 2 --since we already know we should have exactly two rows AND COUNT(*) = ( SELECT COUNT(*) FROM table1 b WHERE b.groupid = a.groupid GROUP BY b.groupID ) 

Answer 6

 id | groupid | memberid 1 | g1 | m1 2 | g1 | m2 3 | g2 | m1 4 | g2 | m2 5 | g2 | m3 select GRPID from arcv where GRPID in ( select GRPID from arcv group by GRPID having count(1)=2) and memberid in ('m1','m2')