[patient_details]
SELECT
	p.last_name,
	p.first_name,
	p.dob,
	p.nhs_number,
	r.name AS 'GP',
	rs.display_name AS 'practice',
	pd.address,
	pd.post_code
FROM outreach.patient_dispatch_detail pdd
	JOIN hilis4.patients p on pdd.patient_id = p.id
	JOIN hilis4.patient_demographics pd on pd.patient_id = p.id
	JOIN hilis4.referral_sources rs on pd.practice_id = rs.id
	JOIN hilis4.referrers r on pd.gp_id = r.id
WHERE p.nhs_number = ?

[pack_dispatches]
/* all requests & pack dispatches */
SELECT
    r.id,
    r.request_number,
    r.year,
    fo.`option` as 'outcome',
    rpd.pack_due,
    rpd.pack_sent,
    rpd.return_due
FROM hilis4.requests r
	JOIN hilis4.patient_case pc on r.patient_case_id = pc.id
	JOIN hilis4.patients p on pc.patient_id = p.id
	JOIN outreach.request_pack_dispatch rpd on rpd.request_id = r.id
	JOIN outreach.request_followup rfu on rfu.request_id = r.id
	JOIN outreach.followup_options fo on rfu.followup_option_id = fo.id
WHERE p.nhs_number = ?
ORDER BY r.created_at

[results]
SELECT
    r.id,
    lp.field_label,
    rr.result
FROM outreach.request_results rr
	JOIN outreach.lab_params lp on rr.param_id = lp.id
	JOIN hilis4.requests r on rr.request_id = r.id
	JOIN hilis4.patient_case pc on r.patient_case_id = pc.id
	JOIN hilis4.patients p on pc.patient_id = p.id
WHERE p.nhs_number = ?
ORDER BY r.id