select g.group_id, g.group_name, coalesce(pg.email, ' ') as email, (select count(*) from group_member_map where group_id = g.group_id) as user_count from groups g, parties pg, acs_rels rg, composition_rels rc where g.group_id = pg.party_id and rg.object_id_one = :id and rg.object_id_two = g.group_id and rc.rel_id = rg.rel_id order by upper(g.group_name) select u.user_id, ppu.first_names || ' ' || ppu.last_name as pretty_name, coalesce(u.screen_name, ' ') as screen_name, pu.email, aru.member_state, aru.rel_id from users u, persons ppu, parties pu, acs_rels ru, membership_rels aru where u.user_id = ppu.person_id and u.user_id = pu.party_id and ru.object_id_one = :id and ru.object_id_two = u.user_id and aru.rel_id = ru.rel_id and (aru.member_state <> 'deleted' or aru.member_state is null) order by upper(pretty_name select g.group_id, g.group_name, coalesce(pg.email, ' ') as email, (select count(*) from group_member_map where group_id = g.group_id) as user_count from groups g, parties pg where g.group_id = pg.party_id and not exists ( select 1 from acs_rels ar, composition_rels cr where ar.rel_id = cr.rel_id and ar.object_id_two = g.group_id) order by g.group_name select u.user_id, ppu.first_names || ' ' || ppu.last_name as pretty_name, coalesce(u.screen_name, ' ') as screen_name, pu.email, '' as member_state, null as rel_id from users u, persons ppu, parties pu where u.user_id = ppu.person_id and u.user_id = pu.party_id and not exists ( select 1 from acs_rels ar, membership_rels mr where ar.rel_id = mr.rel_id and ar.object_id_two = u.user_id) order by pretty_name select party_id as group_id, 'All Users' as group_name, email, url, 'f' as is_empty from parties where party_id = -1