postgresql7.1 select l.list_id, l.name, coalesce(su.user_count,0) as user_count, coalesce(uu.users_need_confirmation,0) as users_need_confirmation, coalesce(ue.users_need_email_confirmation,0) as users_need_email_confirmation, (case when l.expiration_date > current_timestamp then 'f' else 't' end) as expired_p, acs_permission__permission_p(l.list_id, :user_id, 'admin') as admin_p from ml_mailing_lists l left outer join (select count(*) as user_count, m.list_id from ml_mailing_list_user_map m where m.subscribed_p = 't' group by m.list_id) su on (su.list_id = l.list_id) left outer join (select count(*) as users_need_confirmation, m2.list_id from ml_mailing_list_user_map m2 where m2.confirmed_p = 'f' group by m2.list_id) uu on (uu.list_id = l.list_id) left outer join (select count(*) as users_need_email_confirmation, m2.list_id from ml_mailing_list_user_map m2, users u where m2.subscribed_p = 'f' and m2.user_id = u.user_id and u.email_verified_p = 'f' group by m2.list_id) ue on (ue.list_id = l.list_id) where l.package_id = :package_id and acs_permission__permission_p (l.list_id, :user_id, 'read') = 't' order by lower(l.name) select mail_class_id, name, acs_permission__permission_p(mail_class_id, :user_id, 'admin') as admin_p from ml_mail_classes where package_id = :package_id and acs_permission__permission_p (mail_class_id, :user_id, 'read') = 't' order by lower(name)