nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Listing groups and their members

Michael_Fennell

Hi ForumI am trying to list all the groups of the system and their members which i am doing using the following sql"SELECT k.ID AS GROUPID ,k.NAME AS GROUPNAME ,k.TYPE AS GROUPTYPE ,c.ID AS USERID ,c.LASTNAME AS USERLASTNAME ,c.NAME AS USEREMAIL ,kc.idFROM kuafchildren kc ,kuaf k ,kuaf cWHERE kc.ID = k.IDAND k.TYPE = 1AND c.ID = kc.CHILDIDORDER BY c.ID ,k.ID"Of course the problem i have is this returns only groups that have members where as i would like to return the groups without members too, anyone know how i can do this or if it can be done, i guess basically what sql can i use to list all group names and the group members if they exist.Any help would be appreciated.RegardsMike.

Find more posts tagged with

Comments

Narasimha_Rao

Message from Narsi <narsi@supaisystems.com> via eLink

Hi Mike,

I have modified your query. Try below one.

(SELECT k.ID AS GROUPID ,k.NAME AS GROUPNAME ,(CASE WHEN k.ID = c.ID THEN 'Group' ELSE 'User' END) AS 'TYPE' ,c.ID AS USERID ,c.LASTNAME AS USERLASTNAME ,c.NAME AS USERNAME FROM kuafchildren kc ,kuaf k ,kuaf c WHERE kc.ID = k.ID AND k.TYPE = 1 AND c.ID = kc.CHILDID )�

UNION�

(SELECT k.ID AS GROUPID ,k.NAME AS GROUPNAME ,(CASE WHEN k.ID = c.ID THEN 'Group' ELSE 'User' END) AS 'TYPE' ,c.ID AS USERID ,c.LASTNAME AS USERLASTNAME ,CASE WHEN c.NAME = k.NAME THEN ' ' WHEN c.NAME <> k.NAME THEN 'c.NAME' END FROM kuaf k ,kuaf c WHERE c.ID = k.ID AND k.TYPE = 1 )

Thanks and Regards,
Narasimha Rao
Supai Systems,
Bangalore, India.
e-mail� : narsi@supaisystems.com
website: www.supaisystems.com

On Mon, May 16, 2011 at 9:31 PM, eLink Discussion: Live Reports Discussion <livereportsdiscussion@elinkkc.opentext.com> wrote:

Listing groups and their members � Posted by michael.fennell@fsa.gov.uk (Fennell, Michael) on 2011/05/16 11:59
�
Hi Forum

I am trying to list all the groups of the system and their members which i am doing using the following sql

"SELECT k.ID AS GROUPID ,k.NAME AS GROUPNAME ,k.TYPE AS GROUPTYPE ,
c.ID AS USERID ,c.LASTNAME AS USERLASTNAME ,c.NAME AS USEREMAIL ,kc.id
FROM kuafchildren kc ,kuaf k ,kuaf c
WHERE kc.ID = k.ID
AND k.TYPE = 1
AND c.ID = kc.CHILDID
ORDER BY c.ID ,k.ID
"
Of course the problem i have is this returns only groups that have members where as i would like to return the groups without members too, anyone know how i can do this or if it can be done, i guess basically what sql can i use to list
all group names and the group members if they exist.

Any help would be appreciated.

Regards
Mike.
[To reply to this thread, use your normal E-mail reply function.]
Discussion: Live Reports Discussion
Livelink Server: knowledge-wlweb01
To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Michael_Fennell

Hi NarasimhaThanks for your reply, i am using Oracle but i got the idea from your reply anyway so managed to get it working perfectly, here is the code if anyone else needs to use it.Oracle: List all groups and their membersSELECT 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 a.Deleted = 0AND a.Name != 'All Livelink'AND a.Name != 'FSA ALL'ORDER BY a.Name Oracle: List all users and groups they belong to.SELECT k1.FirstName "First Name", k1.LastName "Last Name", k1.Name "Name", k2.Name "Group" FROM KUAF k1, KUAF k2WHERE k2.ID IN (SELECT ID FROM KUAFChildren START WITH ChildID = k1.ID CONNECT BY PRIOR ID = ChildID) AND k1.Deleted = 0 AND k1.Type = 0 AND k2.Deleted = 0 AND k2.Type = 1 ORDER BY k1.Name RegardsMike.

Lenny_Walker

Hi Mike, I tried this in my instance of Content Server 10.0.0 and it didn't seem to work. Would you be able to shed anymore light on how you set it up? As in the parameters etc.?

Thanks in advance

Lenny

Appu_Nair

is your livelink db Oracle if not the "connect by" construct is a oracle specific thing.

From memory and not specific to any db

A group with a certain name like "My Good Group in livelink"

We can write

select name,id from kuaf where deleted=0 and type=1 and name like 'My Good Group in livelink'

If you then want to know who is in that group(it could be users and groups) you should query KUAFCHILDREN and the below query shows you users

select * from kuaf where type=0 and deleted=0 and id in ( select childid from kuafchildren where id=(select id from kuaf where name like 'My Good Group in livelink'))

Now the previous poster using Oracle's neat 'connect by' was taking the guess work out of you by succesively querying any groups in its way who had more to query until it reached no more.

In SQLserver db's you have to write functions or CTE's to do that kind of work

There are examples in this forum for how one would use SQLserver functions to traverse or emulate the Oracle's connect by here is a link for self study.

http://consultingblogs.emc.com/christianwade/archive/2004/11/09/234.aspx

Lenny_Walker

Thank you for the help!