Getting a list of users and what groups they are in

Larry_Hall_(eplus01user1_-_(deleted))

I am trying to run a query that will show each group and the users in that group. I found this query in the KnowledgeBase, but have not been able to modify it to get the needed results. This returns the # of users in each group:SELECT k1.Name "BaseGroup", COUNT (*) "Users" FROM KUAF k1, KUAF k2WHERE k1.ID=k2.GroupID GROUP BY k1.Name ORDER BY k1.NameHow do I make this return the individuals (not the count) that are in each group?

Bhupinder_Singh

Message from Bhupinder Singh via eLinkFor LiveReports that query for which users belonging to which group, youwould need to do a join between KUAF and KUAFChildren. The LivelinkSchema document indicates that the KUAF table stores basic data forusers and groups, containing one record for each user or group; theKUAFChildren table stores the data defining the relationships betweengroups and the members of the group. KUAFChildren has only 2 columns:ChildID and ID. I include a sample SQL query below that may provide whatyou are looking for:===================================================================This will give you the Groups and their Users ordered by the group===================================================================SELECT a.Name "Groups", c.Name "Users" FROM KUAF a, KUAFChildren b, KUAF c WHERE a.ID = b.ID AND b.ChildID = c.ID AND a.Type=1 AND c.Type=0 ORDER BY a.NameNOTE: This is a three way join using the KAUF and KUAFChildren tables.The "Type=1" resticts the a table to just groups and "Type=0" restrictsthe c table to just users.- Bhupinder------------------------------------------------------ Bhupinder Singh, B.Math., B.Ed. Senior Product Specialist, Customer Support Open Text Corporation, Waterloo, Ontario, Canada Customer support e-mail: support@opentext.com Customer Support Telephone: 800-540-7292 ------------------------------------------------------ -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, October 21, 2005 10:20 AMTo: eLink RecipientSubject: Getting a list of users and what groups they are inGetting a list of users and what groups they are inPosted by Hall, Larry O on 10/21/2005 10:18 AMI am trying to run a query that will show each group and the users inthat group. I found this query in the KnowledgeBase, but have not beenable to modify it to get the needed results. This returns the # of users in each group:SELECT k1.Name "BaseGroup", COUNT (*) "Users" FROM KUAF k1, KUAF k2WHERE k1.ID=k2.GroupID GROUP BY k1.Name ORDER BY k1.NameHow do I make this return the individuals (not the count) that are ineach group?[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe/open/2249677Livelink Server:https://knowledge.opentext.com/knowledge/livelink.exeTo Unsubscribe from this Discussion, send an e-mail tounsubscribe.livereportsdiscussion@elinkkc.opentext.com.