sql server 2008 - SQL joining 6 tables -

please take @ image.there 5tables related appointtable appointid. need data same appointment id..what should joining query?can 1 me it?

here generated query(i 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        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             dbo.appointment.appointment_id='46'; 

since appointmnent_id primary key of appointment, table has 1:n relationship 6 tables.

this case joining these 6 tables produce multiple rows duplicate data, it's cartesian product. example if (for 1 id=46), there are:

• 3 rows patientinvestigation
• 6 rows patienttreatmentmedicine
• 4 rows patientfindings
• 2 rows patientdiagnosis
• 2 rows patientcc
• 5 rows patientadvice

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

it's better if 6 separate queries 1 join 1 (of 6) tables in each query (and 1 more query data base table appointment). , combine results of 6 queries in application code. example base query , query join first table:

base table:

select      a.appointment_id,      a.patient_id      appointment     a.appointment_id = 46 

join-1 patientinvestigation:

select      pi.investigation_name,      pi.investigation_id      appointment       join     patientinvestigation pi         on pi.appointment_id = a.appointment_id     a.appointment_id = 46