Why can a medication drug record exist without a matching issue record?

A row in medication_drug_record can exist without a matching row in medication_issue_record.

This is expected behaviour in EMIS Web and can occur in production. It may also appear more frequently in non-production or test data, depending on how local test workflows are configured.

Why this happens

A medication_drug_record represents the authorised prescription record. A medication_issue_record represents an actual issue event for dispensing.

Common scenarios include:

• A prescription is created and authorised, but not issued at that time.
• A user can create or authorise prescriptions but does not have permission to issue them, so issue happens later by another user.
• A GP authorises medication in advance and only issues once the patient confirms they need it.
• The prescription is never issued.

What this means for analysis

Do not assume one-to-one coverage between the two models.

• Use medication_drug_record when your question is about prescribing intent or authorised medication records.
• Use medication_issue_record when your question is about medication actually issued for dispensing.

Recommended query pattern

Use a LEFT JOIN from drug records to issue records when you need to identify both issued and non-issued prescriptions.

SELECT
  dr.drug_record_id,
  dr.patient_id,
  dr.effective_datetime AS drug_effective_datetime,
  ir.issue_record_id,
  ir.effective_datetime AS issue_effective_datetime
FROM explorer_open_safely.medication_drug_record AS dr
LEFT JOIN explorer_open_safely.medication_issue_record AS ir
  ON dr.drug_record_id = ir.drug_record_id

If your study is specifically about issued medication, filter the joined output to rows where ir.issue_record_id IS NOT NULL.