mimic-table-relationships

MIMIC-IV Table Relationships

Understanding the identifier hierarchy and table relationships is essential for correct query construction. Incorrect joins can cause data duplication or missing records.

When to Use This Skill

• Writing complex queries joining multiple tables
• Understanding why queries return unexpected row counts
• Debugging duplicate or missing data issues
• Learning MIMIC-IV data structure

Identifier Hierarchy

subject_id (patient)
    └── hadm_id (hospital admission)
            └── stay_id (ICU stay)
                    └── Events (chartevents, labevents, etc.)

subject_id

• Unique per patient
• Persists across all hospitalizations and ICU stays
• Links to: patients table

hadm_id

• Unique per hospital admission
• One patient can have multiple hadm_ids (readmissions)
• Links to: admissions, diagnoses_icd, prescriptions, most lab/hospital tables

stay_id

• Unique per ICU stay
• One hospital admission can have multiple stay_ids (ICU readmission)
• Links to: icustays, chartevents, ICU-specific tables

Core Table Relationships

Hospital Module (mimiciv_hosp)

patients             -- 1 row per subject_id
    |
    +-- admissions   -- 1 row per hadm_id
    |       |
    |       +-- diagnoses_icd
    |       +-- procedures_icd
    |       +-- prescriptions
    |       +-- labevents
    |       +-- microbiologyevents
    |
    +-- transfers    -- Multiple per hadm_id (ward movements)

ICU Module (mimiciv_icu)

icustays            -- 1 row per stay_id
    |
    +-- chartevents  -- Vitals, assessments
    +-- inputevents  -- Medications, fluids
    +-- outputevents -- Urine, drains
    +-- procedureevents
    +-- datetimeevents

Common Join Patterns

Patient -> Hospital -> ICU

SELECT p.subject_id, a.hadm_id, ie.stay_id
FROM mimiciv_hosp.patients p
INNER JOIN mimiciv_hosp.admissions a
    ON p.subject_id = a.subject_id
INNER JOIN mimiciv_icu.icustays ie
    ON a.hadm_id = ie.hadm_id;