如何简化多对多关系之间的查询

I was wondering how to simplify this query, here(http://sqlfiddle.com/#!3/2789c/4) you have the complete example

我想知道如何简化这个查询，这里（http://sqlfiddle.com/#!3/2789c/4）你有完整的例子

SELECT distinct (R.[roleId])
FROM [Role] R
LEFT  JOIN [userRole] U ON R.[roleId] = U.[roleId]
WHERE R.RoleID NOT IN(
  SELECT [roleId] 
  from [dbo].[userRole] 
  WHERE userId = 2) 

I want to get all the roles that are not assigned to an specific user. I think the inner select could be erase.

我想获得未分配给特定用户的所有角色。我认为内部选择可能是擦除。

Update 1

更新1

After your great help, I could use only one SELECT http://sqlfiddle.com/#!3/2789c/87

在你的大力帮助之后，我只能使用一个SELECT http://sqlfiddle.com/#!3/2789c/87

SELECT R.[roleID]
FROM [Role] R
LEFT  JOIN [userRole] U
ON R.[roleID] = U.[roleID] AND U.userId = @userID
WHERE U.userId IS NULL

3 个解决方案

#1

As simple as it gets:

它很简单：

select roleId
  from Role
except
select roleId
  from userRole
 where userId = 2

#2

SELECT R.roleId
FROM [Role] R
LEFT JOIN [userRole] U ON R.roleId = U.roleId
group by r.roleId
having sum(case when U.userId = 2 then 1 else 0 end) = 0

SQLFiddle demo

#3

You can also try this

你也可以试试这个

Select distinct role.roleID from role , userrole
except 
select roleId from userrole where userID=2

OR

要么

SELECT R.roleId
FROM [Role] R
LEFT JOIN [userRole] U ON R.roleId = U.roleId
except 
select roleId from userrole where userID=2