Check if a list of items already exists in a SQL database

Question

I want to create a group of users only if the same group does not exist already in the database.

I have a GroupUser table with three columns: a primary key, a GroupId, and a UserId. A group of users is described as several lines in this table sharing a same GroupId. Given a list of UserId, I would like to find a matching GroupId, if it exists.

What is the most efficient way to do that in SQL?

Answer 1

Let say your UserId list is stored in a table called 'MyUserIDList', the following query will efficiently return the list of GroupId containing exactly your user list. (SQL Server Syntax)

Select GroupId
 From (
  Select GroupId
       , count(*) as GroupMemberCount
       , Sum(case when MyUserIDList.UserID is null then 0 else 1 End) as  GroupMemberCountInMyList
  from GroupUser 
     left outer join MyUserIDList on GroupUser.UserID=MyUserIDList.UserID
 group by GroupId
 ) As MySubQuery 
Where GroupMemberCount=GroupMemberCountInMyList