• Files
• Commits
• Log
• Graph
• Stats

# referrer for specified location:
SELECT
  `t1`.`name` as 'referrer_name',
  `t1`.`national_code`,
  `t3`.`description` as 'department',
  `t5`.`display_name` as 'location',
  `t5`.`organisation_code`
FROM
  `referrers` t1
  INNER JOIN `referrer_department_map` t2 on `t2`.`referrer_id` = `t1`.`id`
  INNER JOIN `hospital_departments`    t3 ON `t2`.`hospital_department_id` =  `t3`.`id`
  INNER JOIN `parent_organisations`    t4 ON `t2`.`parent_organisation_id` = `t4`.`id`
  INNER JOIN `referral_sources`        t5 ON `t5`.`parent_organisation_id` = `t4`.`id`
WHERE t5.organisation_code = 'NT225'

# referrer by request_id:
SELECT *
FROM
  requests rq
#  LEFT OUTER JOIN request_initial_screen t2 ON (t1.id = t2.request_id)
#  LEFT OUTER JOIN screens t3 ON (t2.screen_id = t3.id)
  JOIN patient_case pc ON (rq.patient_case_id = pc.id)
  JOIN patients pt ON (pc.patient_id = pt.id)
  JOIN referral_sources rs ON (pc.referral_source_id = rs.id)
  JOIN referrers rf ON (rq.referrer_id = rf.id)
  JOIN referrer_department_map rd ON (rf.id = rd.referrer_id AND rs.parent_organisation_id = rd.parent_organisation_id)
  JOIN hospital_departments hd ON (rd.hospital_department_id = hd.id)
WHERE
  rq.id = 1