SQL联接6个表 [英] SQL joining 6 tables

问题描述

请看图片.有5个与appointTable相关的表，其中appointID是appointID. 现在我需要所有具有相同约会ID的数据.什么应该是联接查询?有人可以帮我吗?

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

推荐答案

由于appointmnent_id是Appointment的主键，因此该表与所有6个表都具有1:N关系.

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

在这种情况下，联接到这6个表将产生多行且数据重复，就像Cartesian Product一样.例如，如果(仅一个id=46)有:

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:

• PatientInvestigation的3行
• PatientTreatmentMedicine的6行
• PatientFindings
的4行
• PatientDiagnosis的2行
• PatientCC的2行
• PatientAdvice的5行

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

您将在结果集中获得3x6x4x2x2x5 = 1440 行，而您只需要3 + 6 + 4 + 2 + 2 + 5(+1)= 行.这比需要的行多60倍(并且列更多).

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.

如果您对每个查询中的(6个)表中的一个(表中的一个)进行 6个单独的查询(最好是另外一个查询，以从基表Appointment中获取数据)，则更好.然后在应用程序代码中组合6个查询的结果.基本查询和连接到第一个表的查询的示例:

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:

基本表:

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

加入1位患者调查:

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

这篇关于SQL联接6个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！