donglizhan7848
2013-05-21 22:27
浏览 55
已采纳

MySQL多个WHERE子句到同一列

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.

图片转代码服务由CSDN问答提供 功能建议

我有一个用户表,其中所有信息都是一对一的关系......

  users table 
 
 ------------------------ 
 id |  name 
 ------------------------ 
 800 | 鲍勃史密斯
 801 |  Jane Doe 
 802 |  Fred Flintstone 
 803 |  Barney Rubble 
 804 |  Marge Simpson 
   
 
 

我还有另一个多对一关系表,其中包含所有用户与他们所属的组的用户组映射< / p>

  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 
   
 
 

我正在尝试生成一组不同的用户,这些用户属于2个不同的组。 同时,我试图将这一切保留在一个单独的数组中(我将作为分页参考使用)。

我已经能够通过JOIN语句成功查询两个表...

  $ group_id1 = 25; 
 $ group_id2  = 27; 
 
 $ query = SELECT DISTINCT name,id FROM users INNER JOIN user_usergroup_map.user_id = users.id上的user_usergroup_map WHERE group_id IN('$ group_id1','$ group_id2'); 
  <  / pre> 
 
 

但是,通过这个,我得到了一个明显的用户列表,这些用户属于EITHER group_id 15或group_id 27,而不是属于这两个组的用户。 我知道我已经盯着这个太长时间了,而且我错过了一些简单的逻辑元素,但我只是看不到它,因为我的生活。 如果那里的任何人都可以解释我可能做错的事情,我会非常感激。

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

4条回答 默认 最新

  • dpa89292 2013-05-22 00:53
    最佳回答

    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
                )
    
    评论
    解决 无用
    打赏 举报
查看更多回答(3条)

相关推荐 更多相似问题