Click HERE to see how Saviynt Intelligence is transforming the industry. |
08/23/2023 01:29 AM - edited 08/23/2023 02:09 AM
Hi all
I have a requirement where we want to fetch all the users
Please guide if this is achievable.
Thanks
Solved! Go to Solution.
08/23/2023 01:40 AM
Hi @void0703 ,
Use the below query which will give you user with less than 5 account(Accounts include irrespective of status). If you want only active accounts add account status filter in inner query.
SELECT TAB.USERNAME, TAB.COUNT AS 'NO OF ACCOUNTS' FROM (SELECT U.USERNAME AS USERNAME, COUNT(UA.USERKEY) AS COUNT FROM USERS U, ACCOUNTS A, USER_ACCOUNTS UA WHERE U.USERKEY=UA.USERKEY AND UA.ACCOUNTKEY=A.ACCOUNTKEY GROUP BY UA.USERKEY) TAB WHERE TAB.COUNT < 5