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

问题描述

请看图.有5个表与带有指定ID的指定表相关.现在我需要具有相同约会 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的主键，所以这张表有一个1:N 与所有 6 个表的关系.

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

在这种情况下，加入这 6 个表会产生多行重复数据，就像一个 笛卡尔积.例如如果(对于只有一个 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:

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

• 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) =23 行.这比所需的行数(列数更多)多 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 个)表与一个 JOIN 连接(再执行一个查询以从基表中获取数据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

Join-1 到 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

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