Wrox Home  
Search P2P Archive for: Go

  Return to Index  

sql_language thread: joing one table to 2 other tables


Message #1 by "Arn" <gipanuhot@y...> on Thu, 12 Apr 2001 06:48:51
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.

  Return to Index