NOT TESTED!!

....
rt.RoleKey = (SELECT RoleKey
			FROM wit_Users
			WHERE UserKey = @UserKey AND NOT EXISTS(SELECT * 
                                                                      FROM wit_Rights 
                                                                WHERE UserKey = @UserKey)))

>I have a rights table:
>
>

>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	 0
>

>
>Records 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, RightCode
>

>
>I'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                                    1
>

>
>How do I get the rows for the role, overwriting those rows with the user row where the AllowDeny value is different than the role?