I have a Rights table:

RightKey    RoleKey     UserKey     RightName                 RightCode                 AllowDeny
----------- ----------- ----------- ------------------------- ------------------------- -----------
55          4           NULL        Add User                  sys_add_user              1
56          4           NULL        Edit User                 sys_edit_user             1
57          4           NULL        Remove User               sys_remove_user           1
58          4           NULL        Lock User                 sys_lock_user             1
59          4           1           Add User                  sys_add_user              1
60          4           1           Edit User                 sys_edit_user             1
61          4           1           Remove User               sys_remove_user           0
62          4           1           Lock User                 sys_lock_user             1

The rows with a Null user key represent rights for the Role. I'd like to pull the rows for the Role, then only those Right rows that supercede the role. Right now I have this:

SELECT rt.RightCode, rt.AllowDeny
	FROM sec_Rights rt
	LEFT JOIN sec_RightCategories rc ON rc.CategoryKey = rt.CategoryKey
	LEFT JOIN sec_Roles rl ON rl.RoleKey = rt.RoleKey
	LEFT JOIN sec_Users u ON u.userKey = rt.UserKey
	WHERE (rt.UserKey = @UserKey OR 
		   rt.RoleKey = 
			(SELECT RoleKey
				FROM sec_Users
				WHERE UserKey = @UserKey))
	ORDER BY RightName, RightCode

But it returns this:

RightCode                 AllowDeny
------------------------- -----------
sys_add_user              1
sys_add_user              1
sys_edit_user             1
sys_edit_user             1
sys_lock_user             1
sys_lock_user             1
sys_remove_user           1
sys_remove_user           0

What I want to get back it this:

RightCode                 AllowDeny
------------------------- -----------
sys_add_user              1
sys_edit_user             1
sys_lock_user             1
sys_remove_user           0

I'm not sure how to code this. Could use some help.

Thanks