We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

How to Fetch Username if there is no Role Assigned

AravindK
Regular Contributor
Regular Contributor

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


4 REPLIES 4

rushikeshvartak
All-Star
All-Star

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')


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

AravindK
Regular Contributor
Regular Contributor

@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.


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')


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

dgandhi
All-Star
All-Star

Use this query, for roles where there are no users present , it will display NULL like shown below in the screenshot.

dgandhi_2-1683829639353.png

Thanks

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.