05/10/2023 10:20 PM
Hi Team,
We have an requirement to fetch Rolenames for users and if there is no role assigned then need to display the rolename as empty/blank . We have prepared the below query to get the rolenames but as per our condition we mentioned two users and one user have role assignment and one user doesn't belong to any user.
After running the query, we are getting only one user who have role assinged but not the 2nd user who do not have any role.
select distinct U.USERNAME,U.EMAIL,R.ROLE_NAME FROM role_user_account RO
INNER JOIN users U ON RO.userkey = U.userkey
INNER JOIN roles R ON RO.rolekey = R.rolekey where U.USERNAME IN ('10092731','24321212')
Please help me with the query to get the users who do not have any roles as well.
Thanks,
Aravind
05/10/2023 10:27 PM
select distinct U.USERNAME,U.EMAIL,R.ROLE_NAME FROM roles r left join role_user_account RO on r.rolekey=ro.rolekey left join
users U ON RO.userkey = U.userkey
where U.USERNAME IN ('10092731','24321212')
05/10/2023 11:36 PM
@rushikeshvartak Thanks for reply.
But this is also giving only one user information(who have the roles) but not the 2nd user. We need to both 2 users to be print in the output with thier roles assigned . Even if there is no role assigned also we want to display the username in the output.
05/11/2023 03:55 AM
select distinct U.USERNAME,U.EMAIL,R.ROLE_NAME FROM users u left join role_user_account RO on u.userkey=ro.userkey left join
Roles r ON RO.rolekey= r.rolekey
where U.USERNAME IN ('10092731','24321212')
05/11/2023 11:27 AM
Use this query, for roles where there are no users present , it will display NULL like shown below in the screenshot.
Thanks