SQL Exercise: Find the patients and the physicians who treated them

SQL hospital Database: Exercise-12 with Solution

12. From the following tables, write a SQL query to find the patients with their physicians by whom they received preliminary treatment. Return Patient name as "Patient", address as "Address" 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

 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:

-- SELECTing patient name, address, and physician name
SELECT t.name AS "Patient",
       t.address AS "Address",
       p.name AS "Physician"
-- FROM patient table aliased as t
FROM patient t
-- INNER JOIN with physician table aliased as p based on primary care physician's employeeid
JOIN physician p ON t.pcp = p.employeeid;

Sample Output:

      Patient      |      Address       |    Physician
-------------------+--------------------+------------------
 John Smith        | 42 Foobar Lane     | John Dorian
 Grace Ritchie     | 37 Snafu Drive     | Elliot Reid
 Random J. Patient | 101 Omgbbq Street  | Elliot Reid
 Dennis Doe        | 1100 Foobaz Avenue | Christopher Turk
(4 rows)

Explanation:

The said query in SQL that selects the name and address of patients along with the name of their physician.

The JOIN statement specifies that the patient table should be joined with the physician table using a matching condition where the patient's physician ID pcp matches the physician's employee ID columns.

Alternative Solution:

Using JOIN with WHERE Clause:

-- SELECTing patient name, address, and physician name
SELECT t.name AS "Patient",
       t.address AS "Address",
       p.name AS "Physician"
-- FROM patient table aliased as t, physician table aliased as p
FROM patient t, physician p
-- WHERE clause specifies the join condition based on primary care physician's employeeid
WHERE t.pcp = p.employeeid;

Explanation:

This solution achieves the same result as Solution 1 but uses the traditional comma-separated table list in the FROM clause with join conditions specified in the WHERE clause.

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: Name of the physicians who are not a specialized.
Next SQL Exercise: Patients and doctors who gave them preliminary care.