SQL Query to Show If Two Users in Same Group

Question

I've got a database with 3 tables: users, groups, and groupmembers.

users:
id int
username varchar(50)

groups:
id int
name varchar(50)

groupmembers:
id int
groupid int
user int

Given two user ids, how can I use one query to determine if they share membership in any group (not any particular one, but any group). I'd like the query to return null if they share no membership and not null if they do share membership.

Answer 1

Alternately

select count(*) from groupmembers 
  where user=@user1 
    and groupid in 
      (select groupid from groupmembers where user=@user2)

That will return a count of groups in common. You can test for the count by replacing count*) with the following:

IF(count(*)>0,1,Null)

Answer 2

Here's a SQL that will return the groups that both users are a member of

SELECT g1.id
FROM groupmembers g1 join groupmembers g2 on g1.id = g2.id
WHERE g1.user = @user1
AND g2.user = @user2;

If want to just return '1' or 'Null' you could wrap this as follows

With groupcount as (
    SELECT count(*) gc
    FROM groupmembers g1 join groupmembers g2 on g1.id = g2.id
    WHERE g1.user = @user1
    AND g2.user = @user2)
SELECT Case When gc = 0 Then Null Else 1 End as SharedGroups
FROM groupcount;