CODEWITHSUNDEEP
mysqlsql
Frank
Frank

Reputation: 19

In a typical Many-to-Many Relationship scenario, says user, group and user_group, how to query groups that contain specified users?

Assuming an MYSQL database with a User, a Group and a UserGroup table having the following fields:

User              Group
-----------       ------------
UserId (PK)       GroupId (PK)
Name              Name
...               ...

       \           /
          UserGroup
         -------------
          UserGroupID (PK)
          UserId (FK)
          GroupId (FK)

How can I use SQL query to return all the groups that contain specified users? Says, I want to find which Group/s contain user1 (UserId = 1) and user2 (UserId = 2).

Upvotes: 1

Views: 127

Answers (2)

dye
dye

Reputation: 3

I don't know if this is what you are looking for

  • join all 3 tables then use where condition to look for a specific user base on user name:
  SELECT ug.UserGroupTestID, 
            ug.Userid,
            ug.GroupId, 
            u.name as UserName,
            g.name as GroupName
    FROM UserGroup ug
    INNER JOIN User u on u.UserId = ug.Userid
    INNER JOIN Group g on g.Groupid = ug.Groupid
    WHERE u.name = 'specified users`

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You can use aggregation:

select ug.groupid
from usergroups ug
where ug.userid in (1, 2)
group by ug.groupid
having count(*) = 2;

You can also do this with a self-join:

select ug.grupid
from usergroups ug join
     usergroups ug2
     on ug2.groupid = ug.groupid and
        ug2.userid = 2 and
        ug.userid = 1;

Or even use exists:

select g.*
from groups g
where exists (select 1 from usergroups ug where ug.groupid = g.id and ug.userid = 1) and
      exists (select 1 from usergroups ug where ug.groupid = g.id and ug.userid = 2) ;

If you are learning SQL, it is worth trying all these out and understanding how they work.

Upvotes: 2

Related Questions