title: Selecting users,groups and site from alfresco database
body: <p>I would like to select users,in which groups they belong to, and their sites. I want it with a single postgre sql query.</p>
  
  <p>I've found  these queries, but i want them combined with join if possible.</p>
  
  <pre><code>select * from alf_permission
  
  select * from ALF_AUTHORITY 
  
  select * from ALF_CHILD_ASSOC where CHILD_NODE_NAME like ‘group%’
  
  select * from ALF_CHILD_ASSOC where QNAME_LOCALNAME like ‘GROUP%’
  
  
  
  select
      node_id,
      string_agg(string_value, ',')
  from (
      select
          node_id,
          qname_id,
          (select local_name from alf_qname where id = qname_id) as qname_type,
          string_value
      from alf_node_properties
      where node_id in (
          select id from alf_node 
          where type_qname_id = (
              select id from alf_qname where local_name = 'person'
          )
          and qname_id in (
              select id
              from  alf_qname 
              where local_name in (
                  'username',
                  'firstName',
                  'lastName',
                  'email'     
              )
          )
      )
  ) alf_users
  group by node_id;
  </code></pre>