A JOIN puts two tables side by side, matching rows on a column they share. An encounter carries a patient_id; the patient's name and MRN live in hd_patients:
SELECT e.encounter_id, p.mrn, p.last_name
FROM hd_encounters e
JOIN hd_patients p ON p.patient_id = e.patient_id;
An inner JOIN keeps only the rows that find a match. Before you join, check three things:
- The key. Join on the column that names the same thing in both tables:
patient_id to patient_id, attending_id to provider_id. Encounters and patients both have a payer_id, but matching on it pairs every stay with every patient on that plan.
- The grain. Joining a table with one row per key (a patient, a payer) keeps one row per encounter. Joining a table with several rows per encounter, such as diagnoses, repeats each encounter once per row, and every count or sum after it grows.
- Names are not keys. Two patients can share a name. Group by
patient_id, then show the name.
Inpatient stays with the patient's MRN and name.