SQL-如何检索匹配链接表中所有值的记录

Question

我在数据库中有多对多的关系。例如，我有一个USER table，ROLE Table和USERINROLE table。我在我的网站上搜索需要查找指定角色的用户。例如，我想找回谁是角色的访问记录“读者” 和“作家”

在这里之前我的查询是这样的：

SELECT * FROM User u INNER JOIN UserInRole ur ON 
u.UserId= ur.UserId INNER JOIN Role r ON 
Ur.RoleId = r.RoleId 

的WHERE会是这样的

WHERE roleid IN (1,2) 

但带给用户​​的角色1或角色2，我需要他们两个角色1和角​​色2

我需要RETR ieve用户行和角色一起排了ORM（NHibernate的）

编辑：我使用NHibernate因此，如果有做这个原生的方式，这将是真棒

Answer 1

加入UserInRole的第二个副本。说的第二个副本的别名是UR2，那么你在那里的条件可以

Where ur.roleId = 1 and ur2.roleId = 2 

Answer 2

无法尝试这样的事：

Select * from User u 
inner join UserInRole ur1 on u.UserID = ur1.UserID 
inner join UserInRole ur2 on u.UserID = ur2.UserID 
where ur1.RoleID = 1 
and ur2.RoleID = 2 

未经测试和未优化...

Answer 3

你也可以为此使用INTERSECT运算符。

SELECT * FROM User 
WHERE UserId IN 
(

SELECT UserId FROM UserInRole 
WHERE RoleId =1 
INTERSECT 
SELECT UserId FROM UserInRole 
WHERE RoleId =2 
)