Controlled Drug Prescriptions With Their Prescriber

Easy
199.

Controlled Drug Prescriptions With Their Prescriber

EasyJoinsBasics

MySQL

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

ColumnType
doctor_idint
namevarchar
specialtyvarchar

Primary key: doctor_id.

One row per doctor allowed to prescribe at the hospital.

Table: Drug

ColumnTypeValues
drug_idint
drug_namevarchar
scheduleenumOTC, H, H1, X

Primary key: drug_id.

The formulary. schedule is the drug's legal category; OTC needs no prescription.

Table: Prescription

ColumnType
rx_idint
doctor_idint
drug_idint
patient_idint
rx_datedate

Primary key: rx_id.

One row per drug prescribed. doctor_id and drug_id always exist in their tables.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Doctor

doctor_idnamespecialty
11Priya NairPsychiatry
12Arjun RaoOrthopaedics
13Sneha IyerGeneral Medicine

Drug

drug_iddrug_nameschedule
1Paracetamol 500mgOTC
2Alprazolam 0.5mgH1
3Tramadol 50mgH1
4Morphine 10mgX
5Amoxicillin 500mgH

Prescription

rx_iddoctor_iddrug_idpatient_idrx_date
90011124012025-04-02
90021234022025-04-02
90031314032025-04-03
90041244042025-04-03
90051354052025-04-04
90061124062025-04-05

Output

rx_idrx_datedoctor_namedrug_name
90012025-04-02Priya NairAlprazolam 0.5mg
90022025-04-02Arjun RaoTramadol 50mg
90042025-04-03Arjun RaoMorphine 10mg
90062025-04-05Priya NairAlprazolam 0.5mg
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
doctor_idint
namevarchar
specialtyvarchar

Primary key: doctor_id.

One row per doctor allowed to prescribe at the hospital.

Table: Drug

ColumnType
drug_idint
drug_namevarchar
scheduleenum(OTC, H, H1, X)

Primary key: drug_id.

The formulary. schedule is the drug's legal category; OTC needs no prescription.

Table: Prescription

ColumnType
rx_idint
doctor_idint
drug_idint
patient_idint
rx_datedate

Primary key: rx_id.

One row per drug prescribed. doctor_id and drug_id always exist in their tables.

Examples

Example 1

Doctor

doctor_idnamespecialty
11Priya NairPsychiatry
12Arjun RaoOrthopaedics
13Sneha IyerGeneral Medicine

Drug

drug_iddrug_nameschedule
1Paracetamol 500mgOTC
2Alprazolam 0.5mgH1
3Tramadol 50mgH1
4Morphine 10mgX
5Amoxicillin 500mgH

Prescription

rx_iddoctor_iddrug_idpatient_idrx_date
90011124012025-04-02
90021234022025-04-02
90031314032025-04-03
90041244042025-04-03
90051354052025-04-04
90061124062025-04-05

Output

rx_idrx_datedoctor_namedrug_name
90012025-04-02Priya NairAlprazolam 0.5mg
90022025-04-02Arjun RaoTramadol 50mg
90042025-04-03Arjun RaoMorphine 10mg
90062025-04-05Priya NairAlprazolam 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 →