SQL joining 6 tables

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

• i am taking a look with this approach... - sohel14_cse_ju
• can i make a view for this approach?Then how will be it? - sohel14_cse_ju
• If you want View, you'd have to create 6(+1) views. Why do you need Views? - yper-crazyhat-cubeᵀᴹ
• ok...i think single of query is good for me...thnks... - sohel14_cse_ju

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

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';

• what about if i need data with appointment_id? - sohel14_cse_ju
• copy and paste the select query displayed on the image in your question, so i can modify it if needed. - Jagadeesan
• Please take a look at the query i updated in the main post..i am getting multiple data after joining...is that ok? - sohel14_cse_ju
• i have edited my answer check the query and let me know. - Jagadeesan
• OK..THAT WORKS FINE..NOW I HAVE THE VIEW...TELL ME HOW CAN I QUERY ON THIS VIEW TO GET DATA FOR A COLUMN..LIKE I WANT TO GET ALL THE MEDICINES FROM THE VIEW THAT IS FOR APPOINTMENT ID 46 - sohel14_cse_ju