SQL: select sets containing exactly given members

Question

I'm sure there is a proper word for this which I fail to remember, but the problem is easy to describe: I have a table 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

Above describing two groups, one with m1 and m2 and one with m1,m2 and m3. If I want to select groupids which has members m1,m2 but no other members, how do I do it? The approaches I have tried would also return g2, as m1 and m2 is a subset of them.

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

Answer 1

from your phrase

I want to select groupids which has members m1,m2 but no other members

try this one, the idea behind is to count the total instances of records that match the condition and the where 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 the intersection between those groups that have m1 and m2 and those groups that have exactly two members. SQL has an operator for that:

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 a SQLFiddle demo: http://sqlfiddle.com/#!12/df94d/1

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

Answer 3

there is an issue with this query

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 match groups with m1 only or m2 only. For that we can add another count 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 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') 
      ) ;