SQL Exercise: Patients who have been prescribed some medication

SQL hospital Database: Exercise-36 with Solution

36. From the following table, write a SQL query to determine which patients have been prescribed medication by their primary care physician. Return Patient name as "Patient", and Physician Name as "Physician".

    ssn    |       name        |      address       |  phone   | insuranceid | pcp
-----------+-------------------+--------------------+----------+-------------+-----
 100000001 | John Smith        | 42 Foobar Lane     | 555-0256 |    68476213 |   1
 100000002 | Grace Ritchie     | 37 Snafu Drive     | 555-0512 |    36546321 |   2
 100000003 | Random J. Patient | 101 Omgbbq Street  | 555-1204 |    65465421 |   2
 100000004 | Dennis Doe        | 1100 Foobaz Avenue | 555-2048 |    68421879 |   3

 physician |  patient  | medication |        date         | appointment | dose
-----------+-----------+------------+---------------------+-------------+------
         1 | 100000001 |          1 | 2008-04-24 10:47:00 |    13216584 | 5
         9 | 100000004 |          2 | 2008-04-27 10:53:00 |    86213939 | 10
         9 | 100000004 |          2 | 2008-04-30 16:53:00 |             | 5

 employeeid |       name        |           position           |    ssn
------------+-------------------+------------------------------+-----------
          1 | John Dorian       | Staff Internist              | 111111111
          2 | Elliot Reid       | Attending Physician          | 222222222
          3 | Christopher Turk  | Surgical Attending Physician | 333333333
          4 | Percival Cox      | Senior Attending Physician   | 444444444
          5 | Bob Kelso         | Head Chief of Medicine       | 555555555
          6 | Todd Quinlan      | Surgical Attending Physician | 666666666
          7 | John Wen          | Surgical Attending Physician | 777777777
          8 | Keith Dudemeister | MD Resident                  | 888888888
          9 | Molly Clock       | Attending Psychiatrist       | 999999999

Sample Solution:

SELECT pt.name AS "Ptient",
       p.name AS "Physician"
FROM patient pt
JOIN prescribes pr ON pr.patient=pt.ssn
JOIN physician p ON pt.pcp=p.employeeid
WHERE pt.pcp=pr.physician
  AND pt.pcp=p.employeeid;

Sample Output:

   Ptient   |  Physician
------------+-------------
 John Smith | John Dorian
(1 row) 

Explanation:

This SQL query selects the names of patients and their primary care physicians (PCPs), along with the name of the physician who prescribed medication for the patient.

The first JOIN keyword links the 'patient' and 'prescribes' tables based on the patient and SSN columns. The second JOIN links the 'patient' and 'physician' tables based on the pcp and employeeid columns.

The WHERE clause includes two conditions to filter the results. The first condition ensures that the patient's PCP is also the physician who prescribed medication for the patient. The second condition ensures that the PCP employee ID matches the physician employee ID.

Practice Online

E R Diagram of Hospital Database:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Find nurses who have ever been on call for room 122.
Next SQL Exercise: Patients who have had a procedure costing over $5,000.