Clinician Badge Directory

A hospital is printing a door-access directory. Security wants every current clinician listed with their access badge code instead of their internal id. Some clinicians have not been issued a badge yet, and the badge registry still holds badges of people who have left (their clinician_id no longer exists in clinicians).

Return badge_code and full_name, in that order, with exactly one row for every clinician in clinicians. Use NULL as badge_code for clinicians without a badge, and leave out badges of former staff. Rows may be in any order.

Tables

clinicians — One row per current clinician.

badge_registry — One row per issued access badge; at most one badge per clinician id. May include badges of staff who have left.

Example

clinicians

badge_registry

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Joins · problem 20 of 70 · SQLite

The idea

The output has one row per clinician, so clinicians drives the query. A LEFT JOIN to badge_registry on clinician_id keeps every clinician: when a matching badge exists its code is attached, and when none exists the badge columns are filled with NULL.

Badges of former staff point to ids that are not in clinicians. They never match a left-hand row, so a left join from clinicians simply ignores them. Because clinician_id is unique in the registry, each clinician matches at most one badge and the join cannot duplicate rows.

Flipping the direction (registry on the left) answers a different question, “who owns each badge”, and an inner join answers “who has a badge”. The alternative uses a correlated scalar subquery per clinician; it returns NULL when no badge row is found, which gives the same result here because each clinician has at most one badge.

Solution

SELECT
  b.badge_code,
  c.full_name
FROM clinicians AS c
LEFT JOIN badge_registry AS b
  ON b.clinician_id = c.clinician_id;

Another way

SELECT
  (SELECT b.badge_code
   FROM badge_registry AS b
   WHERE b.clinician_id = c.clinician_id) AS badge_code,
  c.full_name
FROM clinicians AS c;

Why common answers fail

An inner join drops every clinician who has no badge yet.

SELECT b.badge_code, c.full_name
FROM clinicians AS c
JOIN badge_registry AS b ON b.clinician_id = c.clinician_id;

Keeps all badges instead of all clinicians: former staff appear with a NULL name and unbadged clinicians disappear.

SELECT b.badge_code, c.full_name
FROM badge_registry AS b
LEFT JOIN clinicians AS c ON c.clinician_id = b.clinician_id;

What the interviewer is checking

The simplest test of join direction: does the candidate know which side of a LEFT JOIN is preserved and what the unmatched side looks like.

Mistakes to avoid

• Using an inner join and losing unbadged clinicians.
• Putting the registry on the left and listing former staff.
• Filtering the badge table in WHERE, which turns the left join back into an inner join.

Follow-ups to expect

• Show 'PENDING' instead of NULL (COALESCE(b.badge_code, 'PENDING')).
• List only clinicians without a badge (add WHERE b.badge_code IS NULL).
• Find badges that should be deactivated (registry rows with no matching clinician).

In real work

Directory exports, CRM enrichment and any report that must list every entity even when the lookup data is incomplete.

Practice next