Admitted Patients and Their Ward Beds — SQL Easy Problem

Problem statement

A hospital keeps its admitted patients in Patient and bed assignments in BedAllocation. Not every patient has been given a bed yet, and BedAllocation still holds a few rows for patients who were discharged and are no longer in Patient.

Return one row per patient in Patient with the columns patient_id, name, ward and bed_no. A patient without a bed gets NULL in ward and bed_no. Allocations of discharged patients are not part of the answer. Return the rows in any order.

Tables

Table: Patient

Primary key: patient_id.

One row per patient currently admitted.

Table: BedAllocation

Primary key: patient_id.

At most one bed per patient. A patient_id here may belong to someone already discharged.

Examples

Example 1

Patient

BedAllocation

Output

How to solve Admitted Patients and Their Ward Beds

The answer has one row per patient, so Patient drives the query and the bed is optional information attached to it. That is exactly what a LEFT JOIN does: every row of the left table appears, joined to its matching row on the right when there is one, and padded with NULLs when there is not.

An inner join would be wrong in one direction — Arjun and Farhan, who have no bed yet, would disappear. A FULL join would be wrong in the other — the stale allocation for patient 7, discharged long ago, would show up as a row with a NULL name. The LEFT JOIN keeps unmatched rows of Patient only.

BedAllocation has patient_id as its primary key, so a patient matches at most one row and never appears twice. A RIGHT JOIN with the tables swapped is the same query written from the other side, and two scalar subqueries in the select list work as well (a subquery that finds no row yields NULL), though they look the allocation up twice. With the key indexed, the join is one lookup per patient.

Reference solution (MySQL)

SELECT p.patient_id, p.name, b.ward, b.bed_no
FROM Patient p
LEFT JOIN BedAllocation b ON b.patient_id = p.patient_id

Another way

SELECT p.patient_id, p.name, b.ward, b.bed_no FROM BedAllocation b RIGHT JOIN Patient p ON p.patient_id = b.patient_id

Another way

SELECT p.patient_id, p.name,
  (SELECT ward FROM BedAllocation b WHERE b.patient_id = p.patient_id) AS ward,
  (SELECT bed_no FROM BedAllocation b WHERE b.patient_id = p.patient_id) AS bed_no
FROM Patient p