Finding Empty Groups

ray_chance

Does anyone know the sql for locating groups with no users in them? A group structure was created on our livelink system several years ago, and many of the groups have never been populated with users. We would like to clean these up, but we cannot identify them manually as we have over 5000 groups.Thanks

Bhupinder_Singh

Message from Bhupinder Singh via eLinkI think this should work:select a.name from kuaf a where a.type=1 and a.deleted=0 and a.id not in (select distinct b.id from kuafchildren b)NOTES: kuafchildren stores the data defining the relationships between groups and the members of the group. In the above SQL we end upselecting those groups from kuaf that have no entry in the kuafchildren table, i.e. they have no members.type=1 singles out groups instead of usersdeleted=0 singles out existing groups, and prevents deleted groups from appearing- 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, May 28, 2004 8:56 AMTo: eLink RecipientSubject: Finding Empty GroupsFinding Empty GroupsPosted by Newton, Russ on 05/28/2004 08:51 AMDoes anyone know the sql for locating groups with no users in them? A group structure was created on our livelink system severalyears ago, and many of the groups have never been populated with users. We would like to clean these up, but we cannot identify themmanually as we have over 5000 groups.Thanks[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server: https://knowledge.opentext.com/knowledge/livelink.exe