CODEWITHSUNDEEP
sql
RipperDoc
RipperDoc

Reputation: 704

SQL: select sets containing exactly given members

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.

Upvotes: 9

Views: 6285

Answers (6)

user3142652
user3142652

Reputation: 1

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')

Upvotes: 0

manik
manik

Reputation: 85

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
)

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

-- 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 a.x=b.x and a.y=b.y
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 shows a way to get groups listed with their peers, in a highly optimal way. It works well with large databases by decomposing the groups into member counts and takes along the min and max. The groups are quickly pared down using a direct join, and only for the remaining matches is the full table consulted joining back on group ids A and B to finally determine if they are equivalent groups.

If you had 3 similar groups (101,103,104), the sets will appear as three separate rows (101,103),(101,104),(103,104) - because each pair forms a peering, so such a query is best used if you already know one of the groups that you want to find peers for. This filter would fit into the first subquery.

Upvotes: 1

user330315
user330315

Reputation:

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.

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115550

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') 
      ) ;

Upvotes: 1

John Woo
John Woo

Reputation: 263733

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

Upvotes: 11

Related Questions