MySQL多个WHERE子句到同一列

Question

I have a table of users where all information is in a 1-to-1 relationship...

    users table

    ------------------------
    id        |   name
    ------------------------
    800       |  Bob Smith
    801       |  Jane Doe
    802       |  Fred Flintstone
    803       |  Barney Rubble
    804       |  Marge Simpson

I also have another many-to-1 relationship table which houses the usergroup mapping of all users to the group(s) that they are part of

    user_usergroup_map table

    ------------------------
    user_id   |   group_id
    ------------------------
    800       |  16
    800       |  27
    801       |  25
    801       |  27
    802       |  17
    802       |  19
    802       |  22
    802       |  25
    803       |  25
    803       |  27
    804       |  15

I am trying to generate an array of distinct users that are part of 2 distinct groups. At the same time, I am trying to keep this all within a single array (for which I will be using as a pagination reference).

I have been able to successfully query both tables via a JOIN statement...

    $group_id1 = 25;
    $group_id2 = 27;

    $query = SELECT DISTINCT name, id FROM users INNER JOIN user_usergroup_map on user_usergroup_map.user_id=users.id WHERE group_id IN('$group_id1', '$group_id2');

However, with this, I get a distinct list of users who are part of EITHER group_id 15 or group_id 27, instead of those who are part of both groups. I know that I've been staring at this for far too long, and I'm missing some simplistic logical element, but I just can't see it, for the life of me. If anyone out there could shed some light on what I'm probably doing wrong, I'd GREATLY appreciate it.

Answer 1

Assuming you have a constraint on user_usergroup_map table such that a given user cannot be assigned to the same group_id twice, you can do something like this:

Select ...
From users
Where id In (
            Select user_id
            From user_usergroup_map
            Where group_id In(25,27)
            Group By user_id
            Having Count(*) = 2
            )