SQL Server 2012:选择结果不等于字符串

Question

我有用户和角色。这是我查询的一部分：

(select R.RoleName from [dbo].[aspnet_Roles] as R 
    join [dbo].[aspnet_UsersInRoles] as UR on R.RoleId = UR.RoleId
    join [dbo].[aspnet_Users] as U on UR.UserId = U.Userid
    where U.UserName = 'User1') <> 'Doctor' 

但是，由于用户可以具有多个角色（在这种情况下，User1是Doctor和Client），所以我会得到此错误：

子查询返回超过1个值。当子查询跟随=，！=，＆lt;＆lt; =，>，> =或子查询用作表达式时。

我如何使它起作用，因此查询将返回用户的角色，他们的角色都不是Doctor

Answer 1

尝试 NOT EXISTS：

WHERE NOT EXISTS (SELECT *
                  FROM [dbo].[aspnet_Roles] Rsq 
                       JOIN [dbo].[aspnet_UsersInRoles] URsq ON Rsq.RoleId = URsq.RoleId
                       JOIN [dbo].[aspnet_Users] Usq ON UR.UserId = Usq.Userid
                  WHERE Usq.UserName = [Outside Query User ID Field]
                    AND Rsq.RoleName = 'Doctor')

Answer 2

您做错了。尝试这个

 ; WITH CTE AS 
(
    select DISTINCT U.UserName, R.RoleName from [dbo].[aspnet_Roles] as R 
    join [dbo].[aspnet_UsersInRoles] as UR on R.RoleId = UR.RoleId
    join [dbo].[aspnet_Users] as U on UR.UserId = U.Userid
)
SELECT * FROM CTE 
WHERE NOT EXISTS (SELECT 1 FROM CTE CIN WHERE CIN.UserName = CTE.UserName AND CIN.RoleName = 'Doctor')