CODEWITHSUNDEEP
sql-serversql-server-2012
aggicd
aggicd

Reputation: 737

SQL Server 2012: Select results not equal to string

I have users and roles. And this is a part of my query:

(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'

But because a user can have multiple roles (in this case User1 is Doctor and Client) I get this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

How I can make it work so the query will return the roles of the users that neither of their roles are Doctor

Upvotes: 0

Views: 92

Answers (2)

eavom
eavom

Reputation: 1097

You are doing it wrong way. Try this

 ; 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')

Upvotes: 1

Thom A
Thom A

Reputation: 95557

Try 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')

Upvotes: 2

Related Questions