I have four entitities: PATIENT, RECORD, REFERRAL, and DOCTOR. The ER
diagram is shown below
-------------
- PATIENT -
-------------
|1:1
|
|
|
|
|1:N
1:1 ------------- 1:N
-------------------- RECORD ----------------
| ------------- |
| |
| |
| |
|0:1 |
-------------- 1:N 1:1 ------------ 1:1 |
- REFERRAL ------------- DOCTOR -----------------
-------------- ------------
A PATIENT can have 1 to N records. This record may or may not have a
REFERRAL. DOCTOR writes 1 to N REFERRALS as well as RECORDS.
Given the diagram above, I want to retreive the patient together with
their records. I cannot retrieve the records of the patient with referral
and without referral in a single query, so I made two select statements
shown below:
Records without referral:
----------------------------------------------------------------
SELECT T_Patient.*, T_Record.*, T_Doctor.*
FROM T_Patient INNER JOIN
T_Record ON
T_Patient.Patient_ID = T_Record.Patient_ID INNER JOIN
T_Doctor ON
T_Record.Doctor_ID = T_Doctor.Doctor_ID
-------------------------------------------------------------------
Records with referral
-------------------------------------------------------------------
USE Alcantara
SELECT T_Patient.*, T_Record.*, T_Referral.*, T_Doctor.*
FROM T_Patient INNER JOIN
T_Record ON
T_Patient.Patient_ID = T_Record.Patient_ID LEFT OUTER JOIN
T_Referral ON
T_Record.Record_ID = T_Referral.Record_ID LEFT OUTER JOIN
T_Doctor ON
T_Referral.Doctor_ID = T_Doctor.Doctor_ID
---------------------------------------------------------------------
How am I able to combine the two into one SELECT statement?
Please comment on my data model?
Thanks.