DB Exercises
0
← Exercises/
Joins•9 of 12
PrevNext
JoinsIntermediatePRO

Drug-Allergy Interaction Warning Join

Scenario

Patient safety software automatically flags orders when a physician prescribes a medication containing an active chemical ingredient matching a recorded patient allergy.

Task
Join `prescriptions` with `patient_allergies` where the prescription's `active_ingredient` matches the patient's recorded `allergen_substance` for the same patient (`p.patient_id = a.patient_id`). Return: 1. `p.prescription_id` 2. `p.patient_id` 3. `p.medication_name` 4. `a.allergen_substance` 5. `a.severity` Order results by `p.prescription_id` ascending.
Expected Output Format

Flagged prescriptions where prescribed drug substance matches documented patient allergies.

Input Tables & Sample Data0 tables
Loading database schema...
Need a hint?
query.sql|Local Sandbox
Loading SQL editor...
Click "Run" or press ⌘ + Enter to execute your query

About the “Drug-Allergy Interaction Warning Join” SQL Problem

Business Scenario

Patient safety software automatically flags orders when a physician prescribes a medication containing an active chemical ingredient matching a recorded patient allergy.

Expected Output & Criteria

Flagged prescriptions where prescribed drug substance matches documented patient allergies.

Database Table Schema (PostgreSQL DDL)

PostgreSQL 16 WASM

This challenge executes directly against the following relational schema in your browser:

CREATE TABLE patient_allergies (
        allergy_id INT PRIMARY KEY,
        patient_id INT NOT NULL,
        allergen_substance VARCHAR(60) NOT NULL,
        severity VARCHAR(30) NOT NULL
      );

      CREATE TABLE prescriptions (
        prescription_id INT PRIMARY KEY,
        patient_id INT NOT NULL,
        medication_name VARCHAR(80) NOT NULL,
        active_ingredient VARCHAR(60) NOT NULL
      );

Target SQL Clauses & Operators

JOINONORDER BY

Want more Joins practice?

Explore the full syllabus of 12 exercises, syntax guides, and interview pitfalls.

View Joins Track→