Jazz Jazz - 1 month ago 9
SQL Question

Full Outer Join and Null Values: The # of successful medical attentions per Year pear Doctor

I'm working with a database of a (fictitious) hospital. I have the following tables:

Admission --Name Table
Adm_ID --Number of the reception
Pat_ID --Id Patient
Date_Adm --Date reception
Date_Discharge --Discharge Date from the hospital
Health_ID --Health at the moment of discharge


Adm_Med --Name Table
Adm_ID
Med_ID --ID Medical Procedure carried out
Doc_ID --ID Doctor who carried that out.


And I want to make a table that shows the number of successful medical attention per year for each of the doctors. Successful medical attention is define by
Health_ID =20
(
20
is the code for
Healthy
)

My attempt:

There are medical admissions from 1996 to 2001. To make it easy I began with two years only: 1996 and 1997. First I created two separate tables: one that gives the numbers of successful attention in the year 1996 and the other for 1997. For instance for 1996:

SELECT Doc_ID, COUNT(aID) as [Successful 1996]
FROM
(
SELECT a.Adm_ID as aID, Doc_ID, YEAR(Date_Discharge) as yr, Health_ID
FROM Admission a, Adm_Med am
WHERE a.Adm_ID=am.Adm_ID
AND year(date_discharge)=1996
AND Health_ID=20
) temp2
GROUP BY Doc_ID


And the result of the two separate tables is:

enter image description here

Then I tried to join them:

SELECT T1.Doc_ID, [Successful 1996], [Successful 1997]
FROM
(
SELECT Doc_ID, COUNT(aID) as [Successful 1996]
FROM
(
SELECT a.Adm_ID as aID, Doc_ID, YEAR(Date_Discharge) as yr, Health_ID
FROM Admission a, Adm_Med am
WHERE a.Adm_ID=am.Adm_ID
AND year(date_discharge)=1996
AND Health_ID=20
) temp2
GROUP BY Doc_ID
) T1

FULL OUTER JOIN

(
SELECT Doc_ID, COUNT(aID) as [Successful 1997]
FROM
(
SELECT a.Adm_ID as aID, Doc_ID, YEAR(Date_Discharge) as yr, Health_ID
FROM Admission a, Adm_Med am
WHERE a.Adm_ID=am.Adm_ID
AND year(date_discharge)=1997
AND Health_ID=20
) temp2
GROUP BY Doc_ID

) T2
ON T1.Doc_ID=T2.Doc_ID


And gives

enter image description here

As can be see, the line seven gives a successful medical att. in
1997
but with
NULL Doc_ID
for that line, when it should be the
Doc_ID
with number
82
.

I think there a problem with the
FULL OUTER JOIN
, but as a beginner I can't figure out why this is happening and how to fix it.

Answer

The shown leftmost column is derived from T1.Doc_ID. But your subquery T1 does not return a row for those doctors that have no admission in 1996.

Change your query this way:

SELECT coalesce(T1.Doc_ID, T2.Doc_ID), [Successful 1996], [Successful 1997]
...

This way, the doctor id is taken either from T1 or T2 based on where it is not null.

Comments