Query to find the users who have not been assigned roles.

[vaibhav05]

Help me with query that I can use to find the users who have not been assigned roles.

[rushikeshvartak]

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;

[vaibhav05]

I am getting users who already having roles assigned with this query.

I need query to find users for whome roles are not assigned.

[rushikeshvartak · Accepted solution]

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;