从多个表中获取数据 [英] Get data from multiple table

问题描述

Medical_Master

       MedicalID MedicalName
       1(pk)     abc
       2         xyx
       3         pqr

Child_Medical_Master

       ChildMID MedicalID Station Name
       1(pk)    1(fk)     bnb     mfk
       2        1         def     rwr
       3        2         re      wrw      

Medical_Visit

       VTID  PMID  RFMID age
       1(pk) 2(fk) 1      34
       2     2     3      45
       3     3     1      45
       4     1     2      44
       5     2     2      76

医学研究

       UID   VTID  ChildMID SMID   Date  time 
       1(pk) 1(fk) 1        1      kk    jdj
       2     2     3        2      kdf   lfl
       6     3     2        3      rgr   rtr

Doctor_Master

       RFMID  Doctorname
       1(pk)  mr.john
       2      mr.jack
       3      mr.jim

PAtient_Master

       PMID   Firstname LastNAme
       1(pk)  df         ere
       2      rwe        rwer
       3      rwr        fwr

Study_Master

       SMID   MedicalID Description Duration
       1(pk)  1(fk)     fdf         efe
       2      1         ddf         dfdf
       3      2         df          ef

我想要表中的这些列，我的正确查询应该如何?

I want these columns from tables how should be my correct query?

UID,PMID,FIRSTNAME,LASTNAME,AGE,MEDICALNAME,DESCRIPTION,STATION,DATE,DoctorName

我在 7 个表上使用了内连接，但没有得到正确的数据.(医生名字在重复)

i have use inner join on 7 tables but not getting correct data.(Doctorname is repeating)

推荐答案

虽然我不确切知道您想要什么最终结果，但下面的查询和解释可能有助于解决您的问题.拥有额外的样本数据和你想要的结果样本会有所帮助，但这是我的尝试.

While I do not know exactly what you want for the final result, the query and explanation below might help clear up your issues. Having additional sample data and a sample of the result you want would be helpful but here is my attempt.

我从一个较小的查询开始，并设法添加表.第一次查询，这不会为您提供大量数据，但会为您提供最终产品的 ChildMID、MedicalId 和 Station.您需要 ChildMID 和 MedicalId 来连接更多表.(SQL 小提琴):

I started with a smaller query and worked out to add tables. First query, this does not get you a lot of data but gets you the ChildMID, MedicalId and Station for your final product. You need the ChildMID and MedicalId to join more tables. (SQL Fiddle):

SELECT *
FROM Medical_Master mm
INNER JOIN Child_medical_Master cmm
  ON mm.MedicalID = cmm.MedicalId

下一个表我在 ChildMID 上添加了 Medical_Study 表连接，这是您刚刚从上一个连接中获得的.这将为您提供最终产品中的 UID (SQL Fiddle).

The next table I added the Medical_Study table joining on the ChildMID that you just got from your previous join. This will get you the UID in your final product (SQL Fiddle).

SELECT *
FROM Medical_Master mm
INNER JOIN Child_medical_Master cmm
  ON mm.MedicalID = cmm.MedicalId
INNER JOIN Medical_Study ms
  ON cmm.ChildMID = ms.ChildMID

在下一个连接中，我添加了 Study_Master 表.在这里，我做了一些稍微不同的事情，因为我在两个不同的字段上加入了表格.对于这个查询，我加入了 SMID 字段以验证研究是否正确，但我也加入了 MedicalID 字段以确保它使用正确的医疗记录.(请参阅 SQL Fiddle)如果您没有加入两个字段，那么您将获得一条记录对于 Medical_Study 中的每个项目，但您似乎想要具有匹配的 Study 和 Medical 的项目.

In the next join I added the Study_Master table. Here I did something slightly different in that I JOINed the table on two separate fields. For this query I joined on both the SMID field to verify that the Study was correct but I also joined on the MedicalID field to make sure that it was using the correct Medical record. (See SQL Fiddle) If you do not join on both fields then you will get one record for each item in the Medical_Study but it seems like you want the items with both the Study and Medical that are matching.

SELECT *
FROM Medical_Master mm
INNER JOIN Child_medical_Master cmm
  ON mm.MedicalID = cmm.MedicalId
INNER JOIN Medical_Study ms
  ON cmm.ChildMID = ms.ChildMID
INNER JOIN Study_Master sm
  ON ms.SMID = sm.SMID
  AND mm.MedicalID = sm.MedicalID  -- results change if you remove this line

下一个查询稍微简单一点，因为您要添加Medical_Visit 和Patient_Master.(SQL 小提琴)

The next query is a bit easier, in that you are adding the Medical_Visit and the Patient_Master. (SQL Fiddle)

SELECT *
FROM Medical_Master mm
INNER JOIN Child_medical_Master cmm
  ON mm.MedicalID = cmm.MedicalId
INNER JOIN Medical_Study ms
  ON cmm.ChildMID = ms.ChildMID
INNER JOIN Study_Master sm
  ON ms.SMID = sm.SMID
  AND mm.MedicalID = sm.MedicalID
INNER JOIN Medical_Visit mv
  ON ms.VTID = mv.VTID
INNER JOIN Patient_Master pm
  ON mv.PMID = pm.PMID

要添加的最后一张表是 Doctor_Master，它将为您提供医生的名字.(SQL Fiddle) 在添加 Doctor_Master 之前使用提供的示例数据code> table 你有一条记录，但最后有 3 条记录.这是因为 PMID 在某个时候已经看过所有 3 位医生.

Final table to add is the Doctor_Master which will get you the Doctor's name. (SQL Fiddle) Using the sample data provided before you add the Doctor_Master table you have one record but end up with 3 in the end. This is because the PMID has seen all 3 doctor's at some point.

SELECT *
FROM Medical_Master mm
INNER JOIN Child_medical_Master cmm
  ON mm.MedicalID = cmm.MedicalId
INNER JOIN Medical_Study ms
  ON cmm.ChildMID = ms.ChildMID
INNER JOIN Study_Master sm
  ON ms.SMID = sm.SMID
  AND mm.MedicalID = sm.MedicalID
INNER JOIN Medical_Visit mv
  ON ms.VTID = mv.VTID
INNER JOIN Patient_Master pm
  ON mv.PMID = pm.PMID
INNER JOIN Doctor_Master dm
  ON mv.RFMID = dm.RFMID

删除您不希望最终查询的所有字段是:

Removing all of the fields you do not want the final query is:

SELECT ms.UID,
    pm.PMID,
    pm.firstname,
    pm.lastname,
    mv.age,
    mm.medicalname,
    sm.description,
    cmm.station,
    ms.[date],
    dm.doctorname
FROM Medical_Master mm
INNER JOIN Child_medical_Master cmm
    ON mm.MedicalID = cmm.MedicalId
INNER JOIN Medical_Study ms
    ON cmm.ChildMID = ms.ChildMID
INNER JOIN Study_Master sm
    ON ms.SMID = sm.SMID
    AND mm.MedicalID = sm.MedicalID
INNER JOIN Medical_Visit mv
    ON ms.VTID = mv.VTID
INNER JOIN Patient_Master pm
    ON mv.PMID = pm.PMID
INNER JOIN Doctor_Master dm
    ON mv.RFMID = dm.RFMID

我认为这会产生您正在寻找的结果.

I think this produces the result that you are looking for.

这篇关于从多个表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！