Definition

Purpose

The organisation_location model extracts organisation-to-main-location mappings from the anonymised extract. It establishes the primary location for each organisation used for service delivery.

Information

This intersection table identifies each organisation’s main location, providing the primary site reference for appointment scheduling and service delivery. Each organisation has at most one main location marked with the is_main_location_flag.

• emis_organisation_guid (PK): The unique identifier for the organisation
• emis_main_location_guid (FK): Links to the primary location for the organisation

Examples

Get all organisation-location mappings for an organisation

SELECT *
FROM hive.explorer_recruit_anon.organisation
WHERE organisation = 'CDB-0001'
LIMIT 100;

Find organisations with a main location

SELECT
  emis_organisation_guid,
  emis_main_location_guid,
  is_main_location_flag
FROM hive.explorer_recruit_anon.organisation
WHERE main_location_guid IS NOT NULL
LIMIT 100;

Join with location details

SELECT
  org.emis_organisation_guid,
  org.emis_main_location_guid,
  loc.location_type_description,
  loc.open_date
FROM hive.explorer_recruit_anon.organisation org
LEFT JOIN hive.explorer_recruit_anon.location loc
  ON org.main_location_guid = loc.emis_location_guid
  AND org.organisation = loc.organisation
LIMIT 100;