Fetch all the users

[void0703]

Hi all 

I have a requirement where we want to fetch all the users

Please guide if this is achievable.

Thanks

[pmahalle · Accepted solution]

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