RightKey CategoryKey RoleKey UserKey RightName RightCode AllowDeny ------------------------------------------------------------------------------------------- 1 1 NULL NULL Add User sys_add_user 1 2 1 NULL NULL Edit User sys_edit_user 1 3 1 NULL NULL Remove User sys_remove_user 1 4 1 NULL NULL Lock User sys_lock_user 1 5 1 1 NULL Add User sys_add_user 1 6 1 1 NULL Edit User sys_edit_user 1 7 1 1 NULL Remove User sys_remove_user 1 8 1 1 NULL Lock User sys_lock_user 1 9 1 NULL 1 Lock User sys_lock_user 0Records with both RoleKey and UserKey = null are default records. I want to pull the records for the user and those pertaining to the role the user is in, so I wrote this query:
SELECT rt.RightCode, rt.AllowDeny FROM wit_Rights rt LEFT JOIN wit_RightCategories rc ON rc.CategoryKey = rt.CategoryKey LEFT JOIN wit_Roles rl ON rl.RoleKey = rt.RoleKey LEFT JOIN wit_Users u ON u.userKey = rt.UserKey WHERE (rt.UserKey = @UserKey OR rt.RoleKey = (SELECT RoleKey FROM wit_Users WHERE UserKey = @UserKey)) ORDER BY RightName, RightCodeI'm getting back duplicate rows for the 'sys_lock_user' row:
RightCode AllowDeny -------------------------------------------------- ----------- sys_add_user 1 sys_edit_user 1 sys_lock_user 1 sys_lock_user 0 sys_remove_user 1How do I get the rows for the role, overwritting those rows with the user row where the AllowDeny value is different than the role?