postgresql7.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 child1 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 on child1.community_id = class.class_instance_id, dotlrn_communities_all child2 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 on child2.community_id = parent_class.class_instance_id, acs_objects o where child.archived_p = 't' and child1.archived_p = 't' and child2.archived_p = 't' and child1.community_id = child.community_id and child2.community_id = child.community_id and o.object_id = child.community_id order by child.pretty_name