MySQL query to fetch OpenMRS ICD-10 diagnoses for Haiti

openmrs_fetch_icd10_diagnoses.sql

SELECT
  data.uuid,
  GROUP_CONCAT(DISTINCT data.name SEPARATOR '; ') AS name,
  data.set_name,
  GROUP_CONCAT(DISTINCT data.code SEPARATOR '; ') AS 'Mapping'
FROM
(
  SELECT
    c.uuid,
    (
      SELECT uuid
      FROM concept
      WHERE concept.concept_id = concept_set.concept_set
    ) AS set_uuid,
    cn.name,
    cclass.name AS class,
    map.source AS source,
    map.code AS code,
    (
      SELECT name
      FROM concept_name
      WHERE concept_name.concept_id = concept_set.concept_set AND ((locale = 'en') AND locale_preferred = 1)
    ) AS set_name
  FROM concept AS c
  LEFT JOIN
  (
    SELECT
      concept_class.name,
      concept_class.concept_class_id
    FROM concept_class
  ) AS cclass
  ON c.class_id = cclass.concept_class_id
  LEFT JOIN
  (
    SELECT
      concept_name.concept_id,
      CONCAT(concept_name.name, ' (', concept_name.locale, ')') AS name
    FROM concept_name
    WHERE ((locale = 'en') OR (locale = 'fr')) AND locale_preferred = 1
  ) AS cn
  ON c.concept_id = cn.concept_id
  LEFT JOIN
  (
    SELECT
      concept_reference_map.concept_id,
      concept_reference_term.code AS code,
      (
        SELECT name FROM concept_reference_source
        WHERE concept_reference_source.concept_source_id = concept_reference_term.concept_source_id
      ) AS source
    FROM concept_reference_map
    LEFT JOIN concept_reference_term
    ON concept_reference_term.concept_reference_term_id = concept_reference_map.concept_reference_term_id
  ) AS map
  ON c.concept_id = map.concept_id
  LEFT JOIN concept_set
  ON concept_set.concept_id = c.concept_id
) AS data
WHERE data.source = 'ICD-10-WHO' and class = 'Diagnosis'
GROUP BY data.uuid