AnsweredAssumed Answered

SQL/NSQL to query User Info, Primary Role, Associated Access Group, Status

Question asked by KartheepanR on Apr 20, 2010
Latest reply on Apr 20, 2010 by Dave
Hi All,  I am trying to develop a portlet that will allow PMO coordinators the ability to view Users/Resources and the primary role,group, and status.  We have developed a SQL query but the result set is not complete.We have 1483 users in the system, and using that as the primary table, we are joining to the other tables. However, once the query is run, we only get back 908 rows - only records that show users associated with groups. The problem is, I am also trying to show those users not associated to groups. We tried changing the joins, but we always keep getting back 908 rows.   SELECT  
 U.id AS USERS_USER_ID,
 U_GROUPS.USER_ID AS GROUPS_USER_ID ,
 U.User_NAME,
 U_GROUPS.GROUP_ID AS GROUPS_GROUP_ID,
 V.ID AS VIEW_GROUP_ID,
 V.Group_Name,
 V.GROUP_ROLE_TYPE
 
FROM
 Srm_Resources R
    Left Outer Join Cmn_Sec_Users U
          On r.USER_ID = U.Id
    Left Outer Join Cmn_Sec_User_Groups U_GROUPS
          On U_GROUPS.USER_ID = U.Id
    Left Outer Join   cmn_sec_groups_v V  
          On V.id = U_GROUPS.GROUP_ID
WHERE
          U_GROUPS.GROUP_ID is not null AND
          V.language_code='en' AND
          V.GROUP_ROLE_TYPE ='GROUP' AND
          U.User_Name Not Like '%admin%' ;
[left]  Has anyone else developed anything similar that shows All users, and the associated groups?  Any suggestions/feedback would be much appreciated.    Thanks,  Kart.    

Outcomes