The following SQL statement can be used to return a list of all users and the roles assigned to each user.
SELECT
R.MM405_USER_NAME
Role_Desc
, MM406_ROLE_ID
Role_Id
, MM406_USER_ID
User_Id
, U.MM405_USER_NAME
User_Name
, U.MM405_RETIRE
Retired
FROM MM406_USER_ROLES
JOIN MM405_USER_MASTER_EXT R
ON R.MM405_USER_ID = MM406_ROLE_ID
AND MM405_USER_TYPE = 'RT'
JOIN MM405_USER_MASTER_EXT U
ON U.MM405_USER_ID = MM406_USER_ID
AND U.MM405_USER_TYPE = 'UT'
ORDER BY
R.MM405_USER_NAME ASC
, U.MM405_USER_NAME ASC
*Content contributed by Ticket Enterprises Limited
Comments
3 comments
Very helpful! Wouldn't it be great if UB could just put a field on the Users screen for the role they are in?
3 upvotes
Extremely helpful, as usual!
0 upvotes
Glad this ancient piece of SQL has come in handy :) I guess the challenge with displaying in a view is the one-to-many relational nature of the data
0 upvotes
Please sign in to leave a comment.