Sql Override Request

Question

I have a table user_profile, module, Role privilege_mapping
The tables privilegiy_mapping as [ID, Module, privilege, userId , RoleID]

1 | Settings | true   | NULL  | 1  
2 | Settings | false  |   1   | 1  
3 | About    | true   | NULL  | 1  
4 | DashBoard| true   | NULL  | 1  

I have a requirement that some user permissions be redefined (on / off) as opposed to their privileges.

for eq: User 1 has the identifier of role 1. In accordance with the settings of role 1 is prohibited. But I have another overridden entry for the user that it is included.

Preference should be granted for user privileges. Therefore, privileges for user 1 must be

2 | Settings  | false |  1   | 1  
3 | About     | true  | NULL | 1  
4 | DashBoard | true  | NULL | 1  

New to sql queries. How can I achieve a result?
Any help will be appreciated

This is what I have done so far. But now it contains both lines

;WITH prvCTE AS
(
    -- Insert statements for procedure here
    (
        SELECT module_id, url, privilege, module_name
        FROM [dbo].[UserProfile] U
        INNER JOIN [dbo].[privilege_mapping] P ON p.user_id = u.id
        INNER JOIN [dbo].[module] M ON p.module_id = M.id
        WHERE U.id = @userID


            EXCEPT

        -- Insert statements for procedure here
        SELECT module_id, url, privilege, module_name
        FROM [dbo].[UserProfile] U
        INNER JOIN [dbo].[role] R ON U.RoleID = R.RoleID
        INNER JOIN [dbo].[privilege_mapping] P ON p.role_id = R.RoleID
        INNER JOIN [dbo].[module] M ON p.module_id = M.id
        WHERE U.id = @userID
    )
    UNION

    SELECT module_id, url, privilege, module_name
    FROM [dbo].[UserProfile] U
    INNER JOIN [dbo].[role] R ON U.RoleID = R.RoleID
    INNER JOIN [dbo].[privilege_mapping] P ON p.role_id = R.RoleID
    INNER JOIN [dbo].[module] M ON p.module_id = M.id
    WHERE U.id = @userID
)
SELECT *
FROM prvCTE

Answer 1

OK. , :

• , NO UserPrivilege RolePrivilege.
• , UserPrivilege RolePrivilege.

3 , .

SubQuery1: UserPrivileges

, :

SELECT * FROM [dbo].[privilege_mapping] WHERE user_id IS NOT NULL

SubQuery2: RolePrivileges

ROLE, :

SELECT * FROM [dbo].[privilege_mapping] WHERE user_id IS NULL

: COALESCE()

, OR ( , ), COALESCE(). ( ) :

SELECT module_id, url, privilege=COALESCE(UP.privilege,RP.privilege), module_name
   FROM [dbo].[UserProfile] U
      LEFT OUTER JOIN (SELECT * FROM [dbo].[privilege_mapping] WHERE user_id IS NOT NULL) UP ON UP.user_id = U.id
      LEFT OUTER JOIN (SELECT * FROM [dbo].[privilege_mapping] WHERE user_id IS NULL) RP ON RP.role_id = U.role_id
      LEFT OUTER JOIN [dbo].[module] M ON COALESCE(UP.module_id,RP.module_id)=M.id
   WHERE U.id = @userID AND M.id IS NOT NULL

?

. , , "". , role_id, . - , , .

?

OK. , , . , where, NULL module_id (, WHERE module_id IS NOT NULL).

, , , LEFT OUTER JOIN, ( INNER JOIN, ).

. , , .