SQL Exercise: Find physicians who are yet to be affiliated

Last update on December 20 2024 11:41:35 (UTC/GMT +8 hours)

10. From the following tables, write a SQL query to find those physicians who are yet to be affiliated. Return Physician name as "Physician", Position, and department as "Department".

 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

 physician | department | primaryaffiliation
-----------+------------+--------------------
         1 |          1 | t
         2 |          1 | t
         3 |          1 | f
         3 |          2 | t
         4 |          1 | t
         5 |          1 | t
         6 |          2 | t
         7 |          1 | f
         7 |          2 | t
         8 |          1 | t
         9 |          3 | t

 departmentid |       name       | head
--------------+------------------+------
            1 | General Medicine |    4
            2 | Surgery          |    7
            3 | Psychiatry       |    9

Sample Solution:

-- SELECTing physician name, position, and department name
SELECT p.name AS "Physician",
       p.position,
       d.name AS "Department"
-- FROM physician table aliased as p
FROM physician p
-- INNER JOIN with affiliated_with table aliased as a based on physician's employeeid
JOIN affiliated_with a ON a.physician = p.employeeid
-- INNER JOIN with department table aliased as d based on affiliated_with's department
JOIN department d ON a.department = d.departmentid
-- WHERE clause to filter results where primary affiliation is false
WHERE primaryaffiliation = 'false';

Sample Output:

    Physician     |           position           |    Department
------------------+------------------------------+------------------
 Christopher Turk | Surgical Attending Physician | General Medicine
 John Wen         | Surgical Attending Physician | General Medicine
(2 rows)

Explanation:

The said query in SQL that selects the name, position, and department of physicians who are not primary-affiliated with a department from the table physician, affiliated_with, and department.

The query links the physician and affiliated_with table based on the physician and employeeid columns, and affiliated_with and department table based on the department and departmentid columns. The affiliated_with table contains the foreign keys to the physician and department tables.

The WHERE clause filters the results and return only those physicians who are not primary-affiliated with a department.

The resulting output will be a table with the column headers labeled "Physician", "Position", and "Department", respectively.

Pictorial presentation:

Alternative Solutions:

Using Implicit INNER JOIN:

-- SELECTing physician name, position, and department name
SELECT p.name AS "Physician",
       p.position,
       d.name AS "Department"
-- FROM physician table aliased as p, affiliated_with table aliased as a, department table aliased as d
FROM physician p, affiliated_with a, department d
-- WHERE clause specifies join conditions and filters by primary affiliation
WHERE a.physician = p.employeeid
  AND a.department = d.departmentid
  AND primaryaffiliation = 'false';

Explanation:

This solution uses the traditional comma-separated table list with join conditions specified in the WHERE clause. The WHERE clause includes conditions for both joins and the filter for primary affiliation being false.

Using LEFT JOIN and IS NULL:

-- SELECTing physician name, position, and department name
SELECT p.name AS "Physician",
       p.position,
       d.name AS "Department"
-- FROM physician table aliased as p
FROM physician p
-- LEFT JOIN with affiliated_with table aliased as a based on physician's employeeid
LEFT JOIN affiliated_with a ON a.physician = p.employeeid
-- LEFT JOIN with department table aliased as d based on affiliated_with's department
LEFT JOIN department d ON a.department = d.departmentid
-- WHERE clause filters results where primary affiliation is false or where there is no affiliation
WHERE primaryaffiliation = 'false'
   OR a.physician IS NULL;

Explanation:

This solution uses LEFT JOINs to include physicians with no affiliation. The WHERE clause filters results based on the condition that primary affiliation is false or there is no affiliation.

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: Physicians who are trained in a special treatment.
Next SQL Exercise: Name of the physicians who are not a specialized.