postgres7.2 select child.community_id, child.pretty_name, child.description, dotlrn_community__url(child.community_id) as url, parent.community_id as parent_community_id, parent.pretty_name as parent_pretty_name, case when parent.community_id is null then null else dotlrn_community__url(parent.community_id) end as parent_url, class.term_name, class.term_year, parent_class.term_name as parent_term_name, parent_class.term_year as parent_term_year, to_char(o.creation_date, 'Mon YYYY') as creation_date, to_char(o.last_modified, 'Mon YYYY') as last_modified from dotlrn_communities_all child left outer join dotlrn_communities_all parent using (community_id), dotlrn_communities_all child left outer join (select i.class_instance_id, t.term_name, t.term_year from dotlrn_class_instances i, dotlrn_terms t where t.term_id = i.term_id) class using (class_instance_id), dotlrn_communities_all child left outer join (select i.class_instance_id, t.term_name, t.term_year from dotlrn_class_instances i, dotlrn_terms t where t.term_id = i.term_id) parent_class using (class_instance_id), acs_objects o where child.archived_p = 't' and o.object_id = child.community_id order by child.pretty_name