agents and linked record counts

gistfile1.txt

(
  select concat('/agents/people/', agent_person.id) as uri,
         name_person.sort_name as heading,
         'Person' as name_type,
		 name_person_source.value as name_source,
		 name_authority_id.authority_id as authority_id,
         links.count as linked_records
  from agent_person
  left outer join name_person on name_person.agent_person_id = agent_person.id
  left outer join enumeration_value as name_person_source on name_person_source.id = name_person.source_id
  left outer join name_authority_id on name_authority_id.name_person_id = name_person.id
  left outer join (
    select agent_person.id as id, count(linked_agents_rlshp.id) as count
    from agent_person
    left outer join linked_agents_rlshp on linked_agents_rlshp.agent_person_id = agent_person.id
    group by agent_person.id
  ) as links on links.id = agent_person.id
  left outer join user on user.agent_record_id = name_person.agent_person_id
  where name_person.is_display_name and not name_person.source_id is null and user.id is null
) union (
  select concat('/agents/families/',agent_family.id) as uri,
         name_family.sort_name as heading,
         'Family' as name_type,
         name_family_source.value as name_source,
         name_authority_id.authority_id as authority_id,
         links.count as linked_records
  from agent_family
  left outer join name_family on name_family.agent_family_id = agent_family.id
  left outer join enumeration_value as name_family_source on name_family_source.id = name_family.source_id
  left outer join name_authority_id on name_authority_id.name_family_id = name_family.id
  left outer join (
    select agent_family.id as id, count(linked_agents_rlshp.id) as count
    from agent_family
    left outer join linked_agents_rlshp on linked_agents_rlshp.agent_family_id = agent_family.id
    group by agent_family.id
  ) as links on links.id = agent_family.id
  left outer join user on user.agent_record_id = name_family.agent_family_id
  where name_family.is_display_name and not name_family.source_id is null and user.id is null
) union (
  select concat('/agents/corporate_entities/',agent_corporate_entity.id) as uri,
         name_corporate_entity.sort_name as heading,
         'Corporate Entity' as name_type,
         name_corporate_entity_source.value as name_source,
         name_authority_id.authority_id as authority_id,
         links.count as linked_records
  from agent_corporate_entity
  left outer join name_corporate_entity on name_corporate_entity.agent_corporate_entity_id = agent_corporate_entity.id
  left outer join enumeration_value as name_corporate_entity_source on name_corporate_entity_source.id = name_corporate_entity.source_id
  left outer join name_authority_id on name_authority_id.name_corporate_entity_id = name_corporate_entity.id
  left outer join (
    select agent_corporate_entity.id as id, count(linked_agents_rlshp.id) as count
    from agent_corporate_entity
    left outer join linked_agents_rlshp on linked_agents_rlshp.agent_corporate_entity_id = agent_corporate_entity.id
    group by agent_corporate_entity.id
  ) as links on links.id = agent_corporate_entity.id
  left outer join user on user.agent_record_id = name_corporate_entity.agent_corporate_entity_id
  where name_corporate_entity.is_display_name and not name_corporate_entity.source_id is null and user.id is null
)