SELECT
DesignationSchools.name AS organization_name,
DesignationSchools.school_code AS organization_school_code,
Designations.designation_schools_id AS organization_identifier,
Designations.id AS program_identifier,
CASE DesignationPrograms.name WHEN '' THEN NULL ELSE DesignationPrograms.name END AS program_name,
Designations.active AS program_designatable,
DesignationDeadlines.hard_deadline AS program_hard_deadline,
DesignationAddresses.city AS program_city,
DesignationAddresses.state AS program_state,
DesignationAddresses.zip AS program_zip,
phorcas_designations.pgy_type AS program_type
FROM Designations
INNER JOIN DesignationPrograms ON Designations.designation_programs_id = DesignationPrograms.id
INNER JOIN DesignationSchools ON Designations.designation_schools_id = DesignationSchools.id
INNER JOIN DesignationDeadlines ON Designations.designation_deadlines_id = DesignationDeadlines.id
INNER JOIN DesignationAddresses ON DesignationPrograms.designation_addresses_id = DesignationAddresses.id
INNER JOIN phorcas_designations ON Designations.id = phorcas_designations.id