SQL Joining 6 Tables

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

Solution 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

Solution 2:

SELECT 
    Appointment.appointment_id, 
    Appointment.patient_id, 
    PatientInvestigaiton.investigation_name, 
    PatientInvestigaiton.investigation_id, 
    PatientTreatmentMedicine.medecine_id, 
    PatientTreatmentMedicine.medicinename, 
    PatientTreatmentMedicine.medicinetype, 
    PatientFindings.finding_id, 
    PatientFindings.finding_value, 
    PatientAdvice.advice_description, 
    PatientCC.cc_value, 
    PatientCC.cc_id, 
    PatientDiagonosis.diagonosis_name, 
    PatientDiagonosis.diagonosis_id
FROM 
    Appointment 
    LEFT OUTER JOIN PatientInvestigaiton     ON Appointment.appointment_id = PatientInvestigaiton.appointment_id AND Appointment.appointment_id='46'
    LEFT OUTER JOIN PatientTreatmentMedicine ON Appointment.appointment_id = PatientTreatmentMedicine.appointment_id 
    LEFT OUTER JOIN PatientFindings          ON Appointment.appointment_id = PatientFindings.appointment_id 
    LEFT OUTER JOIN PatientDiagonosis        ON Appointment.appointment_id = PatientDiagonosis.appointment_id 
    LEFT OUTER JOIN PatientCC                ON Appointment.appointment_id = PatientCC.appointment_id 
    LEFT OUTER JOIN PatientAdvice            ON Appointment.appointment_id = PatientAdvice.appointment_id

Solution 3:

SELECT {TABLE1}.appointment_id,{OTHER FIELDS} FROM {TABLE1}
    JOIN {TABLE2} ON {TABLE1}.appointment_id = {TABLE2}.appointment_id
    JOIN {TABLE3} ON {TABLE1}.appointment_id = {TABLE3}.appointment_id
    JOIN {TABLE4} ON {TABLE1}.appointment_id = {TABLE4}.appointment_id
    JOIN {TABLE5} ON {TABLE1}.appointment_id = {TABLE5}.appointment_id
    JOIN {TABLE6} ON {TABLE1}.appointment_id = {TABLE6}.appointment_id
    JOIN {TABLE7} ON {TABLE1}.appointment_id = {TABLE7}.appointment_id
    JOIN {TABLE8} ON {TABLE1}.appointment_id = {TABLE8}.appointment_id;

Try this:

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 JOIN dbo.PatientInvestigaiton
        ON dbo.Appointment.appointment_id = dbo.PatientInvestigaiton.appointment_id
    LEFT JOIN dbo.PatientTreatmentMedicine
        ON dbo.Appointment.appointment_id = dbo.PatientTreatmentMedicine.appointment_id
    LEFT JOIN dbo.PatientFindings
        ON dbo.Appointment.appointment_id = dbo.PatientFindings.appointment_id
    LEFT JOIN dbo.PatientDiagonosis
        ON dbo.Appointment.appointment_id = dbo.PatientDiagonosis.appointment_id
    LEFT JOIN dbo.PatientCC
        ON dbo.Appointment.appointment_id = dbo.PatientCC.appointment_id
    LEFT JOIN dbo.PatientAdvice
        ON dbo.Appointment.appointment_id = dbo.PatientAdvice.appointment_id
WHERE
    dbo.Appointment.appointment_id='46';