Controlled Drug Prescriptions With Their Prescriber
Under India's Drugs and Cosmetics Rules, medicines in Schedule H1 and Schedule X must be logged in a separate register with the prescriber's name.
- Difficulty: Easy
- Topics: Joins, Basics
- Dialect: MySQL
- Problem: #199
Problem statement
Under India's Drugs and Cosmetics Rules, medicines in Schedule H1 and Schedule X must be logged in a separate register with the prescriber's name. The pharmacy needs that register from its prescription data.
Return every prescription of a drug whose schedule is H1 or X, with columns rx_id, rx_date, doctor_name (the prescriber's name) and drug_name. Prescriptions of other schedules are left out. Return the rows in any order.
Tables
Table: Doctor
| Column | Type |
|---|---|
| doctor_id | int |
| name | varchar |
| specialty | varchar |
Primary key: doctor_id.
One row per doctor allowed to prescribe at the hospital.
Table: Drug
| Column | Type |
|---|---|
| drug_id | int |
| drug_name | varchar |
| schedule | enum(OTC, H, H1, X) |
Primary key: drug_id.
The formulary. schedule is the drug's legal category; OTC needs no prescription.
Table: Prescription
| Column | Type |
|---|---|
| rx_id | int |
| doctor_id | int |
| drug_id | int |
| patient_id | int |
| rx_date | date |
Primary key: rx_id.
One row per drug prescribed. doctor_id and drug_id always exist in their tables.
Examples
Example 1
Doctor
| doctor_id | name | specialty |
|---|---|---|
| 11 | Priya Nair | Psychiatry |
| 12 | Arjun Rao | Orthopaedics |
| 13 | Sneha Iyer | General Medicine |
Drug
| drug_id | drug_name | schedule |
|---|---|---|
| 1 | Paracetamol 500mg | OTC |
| 2 | Alprazolam 0.5mg | H1 |
| 3 | Tramadol 50mg | H1 |
| 4 | Morphine 10mg | X |
| 5 | Amoxicillin 500mg | H |
Prescription
| rx_id | doctor_id | drug_id | patient_id | rx_date |
|---|---|---|---|---|
| 9001 | 11 | 2 | 401 | 2025-04-02 |
| 9002 | 12 | 3 | 402 | 2025-04-02 |
| 9003 | 13 | 1 | 403 | 2025-04-03 |
| 9004 | 12 | 4 | 404 | 2025-04-03 |
| 9005 | 13 | 5 | 405 | 2025-04-04 |
| 9006 | 11 | 2 | 406 | 2025-04-05 |
Output
| rx_id | rx_date | doctor_name | drug_name |
|---|---|---|---|
| 9001 | 2025-04-02 | Priya Nair | Alprazolam 0.5mg |
| 9002 | 2025-04-02 | Arjun Rao | Tramadol 50mg |
| 9004 | 2025-04-03 | Arjun Rao | Morphine 10mg |
| 9006 | 2025-04-05 | Priya Nair | Alprazolam 0.5mg |
How to solve Controlled Drug Prescriptions With Their Prescriber
A prescription row carries only ids, so the register is built by joining it to both lookup tables: Doctor on doctor_id for the prescriber's name and Drug on drug_id for the drug's name and schedule. Each prescription matches exactly one doctor and one drug, so the joins neither drop nor duplicate rows.
With the three tables side by side the question is a filter on the drug's schedule. IN ('H1', 'X') reads exactly like the rule; listing the excluded schedules instead works only because the enum has four values and would break the day a new schedule is added, which is why the positive list is the better habit.
The same answer can be written with scalar subqueries that look up each name, and an IN subquery for the controlled drugs. With primary-key lookups every form costs one probe per prescription.
Reference solution (MySQL)
SELECT p.rx_id, p.rx_date, d.name AS doctor_name, g.drug_name
FROM Prescription p
JOIN Doctor d ON d.doctor_id = p.doctor_id
JOIN Drug g ON g.drug_id = p.drug_id
WHERE g.schedule IN ('H1', 'X')Another way
SELECT p.rx_id, p.rx_date, (SELECT name FROM Doctor d WHERE d.doctor_id = p.doctor_id) AS doctor_name, (SELECT drug_name FROM Drug g WHERE g.drug_id = p.drug_id) AS drug_name FROM Prescription p WHERE p.drug_id IN (SELECT drug_id FROM Drug WHERE schedule = 'H1' OR schedule = 'X')Another way
SELECT p.rx_id, p.rx_date, d.name AS doctor_name, g.drug_name FROM Doctor d, Drug g, Prescription p WHERE d.doctor_id = p.doctor_id AND g.drug_id = p.drug_id AND g.schedule <> 'OTC' AND g.schedule <> 'H'← Lab Reports Breaching the 24-Hour Turnaround · Completed OPD Visits per Department in March 2025 →