postgresql9.0
select p.package_id,
p.instance_name,
n.node_id,
n.name,
:subsite_url || n.name as url,
(select count(*)
from group_approved_member_map m
where m.rel_type = 'membership_rel'
and m.group_id = ag.group_id) as num_members,
(select min(r2.member_state)
from group_member_map m2,
membership_rels r2
where m2.group_id = ag.group_id
and m2.member_id = :untrusted_user_id
and r2.rel_id = m2.rel_id) as member_state,
g.group_id,
g.join_policy
from site_nodes n,
apm_packages p,
application_groups ag,
groups g
where n.parent_id = :subsite_node_id
and p.package_id = n.object_id
and p.package_key in ('[join [subsite::package_keys] {','}]')
and ag.package_id = p.package_id
and g.group_id = ag.group_id
and (acs_permission__permission_p(p.package_id, :untrusted_user_id, 'read')
or g.join_policy != 'closed')
order by lower(instance_name)