sql server 2008 - SQL joining 6 tables

Question

Please take a look at the image.There are 5tables related to appointTable with appointID. Now i need all the data with same appointment id..what should be the joining query?Can any one help me about it?

Here is the generated query(I am using Left outer Join)

SELECT     dbo.Appointment.appointment_id, dbo.Appointment.patient_id, dbo.PatientInvestigaiton.investigation_name, dbo.PatientInvestigaiton.investigation_id, 
           dbo.PatientTreatmentMedicine.medecine_id, dbo.PatientTreatmentMedicine.medicinename, dbo.PatientTreatmentMedicine.medicinetype, 
           dbo.PatientFindings.finding_id, dbo.PatientFindings.finding_value, dbo.PatientAdvice.advice_description, dbo.PatientCC.cc_value, dbo.PatientCC.cc_id, 
           dbo.PatientDiagonosis.diagonosis_name, dbo.PatientDiagonosis.diagonosis_id

FROM       dbo.Appointment LEFT OUTER JOIN

           dbo.PatientInvestigaiton ON dbo.Appointment.appointment_id = dbo.PatientInvestigaiton.appointment_id LEFT OUTER JOIN
           dbo.PatientTreatmentMedicine ON dbo.Appointment.appointment_id = dbo.PatientTreatmentMedicine.appointment_id LEFT OUTER JOIN
           dbo.PatientFindings ON dbo.Appointment.appointment_id = dbo.PatientFindings.appointment_id LEFT OUTER JOIN
           dbo.PatientDiagonosis ON dbo.Appointment.appointment_id = dbo.PatientDiagonosis.appointment_id LEFT OUTER JOIN
           dbo.PatientCC ON dbo.Appointment.appointment_id = dbo.PatientCC.appointment_id LEFT OUTER JOIN
           dbo.PatientAdvice ON dbo.Appointment.appointment_id = dbo.PatientAdvice.appointment_id

           where dbo.Appointment.appointment_id='46';

Answer 1

Since appointmnent_id is the primary key of Appointment, this table has a 1:N relationship with all 6 tables.

This is the case where joining to these 6 tables will produce multiple rows with duplicate data, it's like a Cartesian Product. For example if (for only one id=46), there are:

• 3 rows for PatientInvestigation
• 6 rows for PatientTreatmentMedicine
• 4 rows for PatientFindings
• 2 rows for PatientDiagnosis
• 2 rows for PatientCC
• 5 rows for PatientAdvice

you'll get 3x6x4x2x2x5 = 1440 rows in the result set, while you only need 3+6+4+2+2+5 (+1) = 23 rows. That is 60 times more rows (and with many more columns) than needed.

It's better if you do 6 separate queries with one JOIN to one (of the 6) tables in each query (and one more query to get the data from the base table Appointment). And combine the results of the 6 queries in the application code. Example for the base query and the query to join to the first table:

Base table:

SELECT 
    a.appointment_id, 
    a.patient_id
FROM 
    Appointment AS a
WHERE
    a.appointment_id = 46

Join-1 to PatientInvestigation:

SELECT 
    pi.investigation_name, 
    pi.investigation_id
FROM 
    Appointment AS a
      JOIN
    PatientInvestigation AS pi
        ON pi.appointment_id = a.appointment_id
WHERE
    a.appointment_id = 46