Separate user role in SQL-CodePudding

I have a table called user_role_link where the column are userid, roleid, bitlive and bitdeleted. This table links to the dbo.user_role where in here I have columns roleid, DescriptionRole

I want to extract all the users with introleid in (256, 308, 313) but not to be part of this roles: introleid NOT IN (225, 228, 229, 230, 231, 232, 233, 236) normally if you have on of this you could have these ones (256, 308, 313).

So what I am looking to achieve is to remove these roles from the users (256, 308, 313) where they don't have the main one (225, 228, 229, 230, 231, 232, 233, 236) By mistake someone granted to these users the roles without having the parent one.

My query is like this

select distinct intuserid
from user_role_link 
where introleid in (256, 308, 313, 314, 484, 485)
  and introleid NOT  IN (225, 228, 229, 230, 231, 232, 233, 236, 237, 239, 240, 241, 242) 
  and bitLive = 1
  and bitDeleted = 0

Please can you let me know how this can be improved.

CodePudding user response：

So you want to remove 3 roles from users, but only when any of those other roles don't exist for them.

Then you could use a NOT EXISTS

DELETE 
FROM user_role_link ur1
WHERE roleid IN (256, 308, 313)
  AND bitLive = 1
  AND bitDeleted = 0
  AND NOT EXISTS (
    SELECT 1
    FROM user_role_link ur2
    WHERE ur2.userid = ur1.userid
      AND ur2.roleid IN (225, 228, 229, 230, 231, 232, 233, 236)
  );