How to Display null if desired value is not found using SQL-CodePudding

I have a system where there are users with multiple roles, some people could have up to 5 different roles assigned to them and i want to build a query where i can display the users name, if they have a specific role and then also if they have a second specific role. If they do then both would populate but if they only have the first role then the second column would appear blank.

I know what I have below is wrong as it will only display the user if they have BOTH roles but is it possible to enter an IF statement or similar whereby the second role column would display the second role or if no other roles are assigned or any other role is assigned it displays a null value?

SELECT USERS.USER_NAME, USER_ROLE.USER_ROLE, USER_ROLE_1.USER_ROLE
FROM SYSTEM.USERS USERS, SYSTEM.USER_ROLE USER_ROLE, SYSTEM.USER_ROLE USER_ROLE_1
WHERE USERS.USER_NAME = USER_ROLE_1.USER_NAME AND USERS.USER_NAME = USER_ROLE.USER_NAME 
      AND ((USER_ROLE.USER_ROLE = 'MASTER') AND (USER_ROLE_1.USER_ROLE='ADMIN'))

Also I don't want duplicates which is why I haven't tried some sort of display user if they have role 1 or role 1 AND role 2 etc like below

SELECT USERS.USER_NAME, USER_ROLE.USER_ROLE, USER_ROLE_1.USER_ROLE
FROM SYSTEM.USERS USERS, SYSTEM.USER_ROLE USER_ROLE, SYSTEM.USER_ROLE USER_ROLE_1
WHERE USERS.USER_NAME = USER_ROLE_1.USER_NAME AND USERS.USER_NAME = USER_ROLE.USER_NAME AND (((USER_ROLE.USER_ROLE = 'MASTER') AND (USER_ROLE_1.USER_ROLE='ADMIN')) OR (USER_ROLE.USER_ROLE = 'MASTER'))

CodePudding user response：

You should learn more about joins. Your syntax may work for you, but the format you are writing it makes it hard to analyze. Try something like this:

SELECT USERS.USER_NAME
    ,USER_ROLE.USER_ROLE
    ,USER_ROLE_1.USER_ROLE
FROM SYSTEM.USERS USERS
    INNER JOIN SYSTEM.USER_ROLE USER_ROLE
        ON USERS.USER_NAME = USER_ROLE.USER_NAME
    LEFT OUTER JOIN SYSTEM.USER_ROLE USER_ROLE_1
        ON USERS.USER_NAME = USER_ROLE_1.USER_NAME
WHERE (((USER_ROLE.USER_ROLE = 'MASTER') 
        AND (USER_ROLE_1.USER_ROLE='ADMIN')
        ) 
    OR (USER_ROLE.USER_ROLE = 'MASTER')
    )

I do not have an idea about what the data looks like here, but the way I laid out how joins can be written with 'ON' clauses should help you. As @Andrew indicated, a LEFT OUTER JOIN seems appropriate for the table that may not have a matching value in every record. You will still output records from the other tables, with the unmatched fields showing 'NULL's.

CodePudding user response：

Try below:

SELECT 
    USERS.USER_NAME,
    RoleQ.FirstRole,
    (CASE WHEN RoleQ.FirstRole IS NULL THEN RoleQ.SecondRole ELSE NULL END) AS SecondRole   
FROM   SYSTEM.USERS         USERS
LEFT JOIN 
(
    SELECT 
        USER_NAME,
        MAX(CASE WHEN USER_ROLE = 'MASTER' THEN USER_ROLE ELSE NULL END) AS FirstRole,
        MAX(CASE WHEN USER_ROLE = 'ADMIN' THEN USER_ROLE ELSE NULL END) AS SecondRole
    FROM SYSTEM.USER_ROLE
    GROUP BY USER_NAME 
) AS RoleQ ON USERS.USER_NAME = RoleQ.USER_NAME