Click HERE to see how Saviynt Intelligence is transforming the industry. |
08/13/2024 05:05 AM
Help me with query that I can use to find the users who have not been assigned roles.
Solved! Go to Solution.
08/13/2024 05:29 AM
SELECT DISTINCT
u.userkey AS userkey,
u.username,
u.displayname AS fullname,
u.email AS email,
u.statuskey
FROM
users u
JOIN
user_accounts ua ON u.userkey = ua.userkey
LEFT JOIN
role_user_account rua ON ua.accountkey = rua.accountkey
WHERE
rua.rolekey IS NULL
AND u.statuskey = '1'
ORDER BY
u.username;
08/13/2024 05:41 AM
I am getting users who already having roles assigned with this query.
I need query to find users for whome roles are not assigned.
08/13/2024 05:49 AM
SELECT DISTINCT
u.userkey AS userkey,
u.username,
u.displayname AS fullname,
u.email AS email,
u.statuskey,
r.role_name
FROM
users u
LEFT JOIN
role_user_account rua ON u.userkey = rua.userkey
LEFT JOIN
roles r ON r.rolekey = rua.rolekey
WHERE
rua.userkey IS NULL
AND u.statuskey = '1'
ORDER BY
u.username;