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)

Do you only want to return information for the MOST RECENT visit? Right now you are reporting on ALL visits, which is resulting in the duplication of your data. — Justin Swanhart
– Justin Swanhart, Commented Aug 3, 2012 at 23:46
@John Based on the sample data above, can you show the expected results? — Taryn
– Taryn, Commented Aug 7, 2012 at 15:32
What is your expected result? And which type of query you need exactly ? — JDGuide
– JDGuide, Commented Aug 9, 2012 at 12:18

Taryn · Accepted Answer · 2012-08-09 12:58:06Z

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.

TJH · Accepted Answer · 2012-07-31 14:57:33Z

2

Njg, just to make this clear the reason why the Doctor's name will be repeating is that they will be visiting more than one patient and each patient will be seen numerous times by that one doctor. So for example for one patient who has been seen 3 times by their doctor they will have 3 "duplicated" lines because the medical_visit table has recorded 3 separate visits.

If you just want to show each patient's details (first name, surname, age, hospital location and doctor) then I would initially remove out the following line:

INNER JOIN Medical_Visit ON Medical_Visit.VTID=Medical_Study.VTID

Then work around the other tables which rely on that join. Have a look and see what that does, you may need to remove other joins until you find out what is duplicating the information. It may be the case that you need to use a subquery in the join to reduce the duplication coming from a table but first identify what table is causing the duplication (I can't use SQLFiddle with your example tables.)

edited Jul 31, 2012 at 14:57

answered Jul 31, 2012 at 14:20

TJH

3091 gold badge6 silver badges18 bronze badges

1 Comment

Henrique Ordine Over a year ago

Good answer. If he wants a Certain Doctor's name to show up in only one row, which Visit should show up on that same row?

Ray 'user1578904' · Accepted Answer · 2012-08-09 13:37:12Z

Building complex queries may not always be the best solution. Working with joins requires that you keep very good control on where you need left/right/full inner/outer joins to get the required result.

A downside of complex queries, besides it takes time to create, you will never be able to understand at a later moment and maintenance will be a nightmare.

Also, what is the result you want to have? I have realized often that I was expecting only one occurrence of a specific value, but in the real scenario it may in some situations be valid to have multiple identical values. See the comment from Tim Hodges.

IMHO it may be a better option to create a few views where data is joined together on a more simple level and then in the end you have a join from those views. In this scenario you will less likely end up confused and be in better control on how the data should look like.

If you still want to move ahead with your current path, please post the expected result (data, not column names) together with the current query you are using (and its incorrect result, describing why/how it is incorrect), for us to help you further with.

Mark Kremers · Accepted Answer · 2012-08-03 15:10:56Z

For what i can make of it, i have the following query

select  u.UID
      , p.PMID
      , p.FIRSTNAME
      , p.LASTNAME
      , v.AGE
      , m.MEDICALNAME
      , u.DESCRIPTION
      , c.STATION
      , s.DATE
      , a.DoctorName
from    medical_master m
        inner join Child_Medical_Master c
          on  m.MedicalID = c.MedicalID
        inner join Medical_Study s
          on  s.ChildMID = c.ChildMID
        inner join Medical_Visit v
          on  v.VTID = s.VTID
        inner join Doctor_Master a
          on  a.RFMID = v.RFMID
        inner join Patient_Master p
          on  p.PMID = v.PMID
        inner join Study_Master u
          on  u.SMID = s.SMID

I get this result

result

u.UID |##| p.PMID |##| p.FIRSTNAME |##| p.LASTNAME |##| v.AGE |##| m.MEDICALNAME |##| u.DESCRIPTION |##| c.STATION |##| s.DATE |##| a.DoctorName

1 |##| 2 |##| rwe |##| rwer |##| 34 |##| abc |##| fdf |##| bnb |##| kk |##| mr.john

6 |##| 3 |##| rwr |##| fwr |##| 45 |##| abc |##| df |##| def |##| rgr |##| mr.john

2 |##| 2 |##| rwe |##| rwer |##| 45 |##| xyx |##| ddf |##| re |##| kdf |##| mr.jim

So, yeah, according to your data you get mr.john 2 times. What i also see is that for a different person you get 2 different age's... that is kinda typical. Have you done a sanity check on your data?? I think it is going wrong somewhere in there...

Himanshu · Accepted Answer · 2012-08-04 05:20:19Z

0

Run this query.

SELECT    MS.UID, PM.PMID, PM.Firstname, PM.LastNAme, MV.age, 
          MM.MedicalName, SM.Description, 
          CMM.Station, MS.Date, DM.Doctorname
FROM      Child_Medical_Master CMM LEFT OUTER JOIN
          Medical_Master MM ON CMM.MedicalID = MM.MedicalID RIGHT OUTER JOIN
          Study_Master SM RIGHT OUTER JOIN
          Doctor_Master DM INNER JOIN
          PAtient_Master PM INNER JOIN
          Medical_Visit MV ON PM.PMID = MV.PMID INNER JOIN
          Medical_Study MS ON MV.VTID = MS.VTID ON DM.RFMID = MV.RFMID ON 
          SM.SMID = MS.SMID ON CMM.ChildMID = MS.ChildMID

See this fiddle.

answered Aug 4, 2012 at 5:20

Himanshu

32.8k32 gold badges117 silver badges137 bronze badges

Comments

vektor · Accepted Answer · 2012-08-09 10:22:46Z

0

Try the one below:

    Select MS.UID,
      PM.PMID,
      PM.FIRSTNAME,
      PM.LASTNAME,
      MV.PMID,
      MV.AGE,
      MM.MedicalName,
      SM.Description
      CMM.Station,
      MS.Date,
      DM.Doxtorname                                     
    From Patient_Master PM 
      inner join Medical_Visit MV On PM.PMID = MV.PMID
      inner join Medical_Study MS On MV.UID = MS.UID
      inner join Study_Master SM On SM.SMID = MS.SMID
      inner join Medical_Master MM On MM.MedicalID = SM.MedicalID
      inner join Child_Medical_Master CMM On CMM.MedicalID = MM.MedicalID
      inner join Doctor_Master DM On Dm.RFMID = MV.RFMID

edited Aug 9, 2012 at 10:22

vektor

2,9648 gold badges46 silver badges74 bronze badges

answered Aug 4, 2012 at 4:52

Sanjay

3422 silver badges9 bronze badges

Collectives™ on Stack Overflow

Get data from multiple table

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related