topic Selecting users,groups and site from database in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/selecting-users-groups-and-site-from-database/m-p/308179#M261309 <HTML><HEAD></HEAD><BODY><SPAN>Hi,</SPAN><BR /><SPAN>I would like to select users,in which groups they are, and their sites.. I wan it with a single postgre sql query.</SPAN><BR /><BR /><SPAN>I've found&nbsp; these queries, but i want them combined with join if possible.</SPAN><BR /><BR /><PRE class="language-none line-numbers"><CODE><BR /><BR />select * from alf_permission<BR /><BR />select * from ALF_AUTHORITY <BR /><BR />select * from ALF_CHILD_ASSOC where CHILD_NODE_NAME like ‘group%’<BR /><BR />select * from ALF_CHILD_ASSOC where QNAME_LOCALNAME like ‘GROUP%’<BR /><BR /><BR /><BR />select<BR />&nbsp;&nbsp;&nbsp; node_id,<BR />&nbsp;&nbsp;&nbsp; string_agg(string_value, ',')<BR />from (<BR />&nbsp;&nbsp;&nbsp;select<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;node_id,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;qname_id,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;(select local_name from alf_qname where id = qname_id) as qname_type,<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string_value<BR />&nbsp;&nbsp;&nbsp;from alf_node_properties<BR />&nbsp;&nbsp;&nbsp;where node_id in (<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select id from alf_node <BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where type_qname_id = (<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select id from alf_qname where local_name = 'person'<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;and qname_id in (<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select id<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp; alf_qname <BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where local_name in (<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'username',<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'firstName',<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'lastName',<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'email'&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)<BR />&nbsp;&nbsp;&nbsp;)<BR />) alf_users<BR />group by node_id;<BR /><BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE></BODY></HTML> Sun, 08 Jun 2014 13:15:34 GMT svetlingulev 2014-06-08T13:15:34Z Selecting users,groups and site from database https://connect.hyland.com/t5/alfresco-archive/selecting-users-groups-and-site-from-database/m-p/308179#M261309 Hi,I would like to select users,in which groups they are, and their sites.. I wan it with a single postgre sql query.I've found&nbsp; these queries, but i want them combined with join if possible.select * from alf_permissionselect * from ALF_AUTHORITY select * from ALF_CHILD_ASSOC where CHILD_NODE_NAME l Sun, 08 Jun 2014 13:15:34 GMT https://connect.hyland.com/t5/alfresco-archive/selecting-users-groups-and-site-from-database/m-p/308179#M261309 svetlingulev 2014-06-08T13:15:34Z Re: Selecting users,groups and site from database https://connect.hyland.com/t5/alfresco-archive/selecting-users-groups-and-site-from-database/m-p/308180#M261310 <HTML><HEAD></HEAD><BODY><SPAN>test</SPAN></BODY></HTML> Sun, 08 Jun 2014 17:12:42 GMT https://connect.hyland.com/t5/alfresco-archive/selecting-users-groups-and-site-from-database/m-p/308180#M261310 svetlingulev 2014-06-08T17:12:42Z