SQL query to find the Users that has role provisioned by an role mapping
I'm trying to create a query that return the MAPPING_NAME from the PER_ROLE_MAPPINGS table and the USER_ID from the PER_USER_ROLES table to know which USER_ID is associated with which MAPPING_NAME. However, these two tables are not directly linked, it is necessary to carry out checks on other tables to reach this junction.
I created an SQL query with the information I found and this was the result:
SELECT prm.MAPPING_NAME, COUNT(pur.USER_ID) AS TOTAL_USER_IDS
FROM PER_ROLE_MAPPINGS prm
LEFT JOIN PER_ROLE_MAPPING_ROLES prmr ON prm.ROLE_MAPPING_ID = prmr.ROLE_MAPPING_ID