Get data from multiple table

Question

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

Medical_Study

       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

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

Answer 1

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.

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

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

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

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

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.