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 these queries, but i want them combined with join if possible.
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;
