Joining 4 tables with good references to each other

user_10863182 0
Viewed 54

I'm staring myself blind on this MYSQL query. It shouldn't be that hard and I do get results, but not the one I want. Thanks a lot for your help!

doctor

dctr_id | dctr_name | ...
--------------------------
60      | Bezant

visits

vist_id | dctr_id| prsnl_id | visit_date | ...
-----------------------------------------------
1       | 60      | 86      | 2018-12-31

accidents

acc_id | dctr_id | prsnl_id| acc_date | ...
--------------------------------------------
51     | 60      | 86      | 2018-12-25
55     | 60      | 86      | 2018-12-20

personell

prsnl_id | prsnl_name | ...
---------------------------
79       | test_name2
86       | test_name

I tried different kinds of queries but none do the trick. distinct, grouping, ...

I get this result:

dctr_id | dctr_name | visit_id | visit_date | acc_id | acc_date | prsnl_id | prsnl_name
-----------------------------------------------------------------------------------------
60      | Bezant    | 1        | 2018-12-31 | 51     | 2018-12-25 | 79     | test_name2
60      | Bezant    | 1        | 2018-12-31 | 51     | 2018-12-25 | 79     | test_name2
60      | Bezant    | 1        | 2018-12-31 | 55     | 2018-12-20 | 86     | test_name1


SELECT DISTINCT dctr.dctr_id 
              , dctr.dctr_name 
              , vst.visit_id
              , vst.visit_date
              , acc.acc_id
              , acc.acc_date,prsnl.prsnl_id
              , prsnl.name  
          FROM doctor dctr
          LEFT 
          JOIN visits vst 
           ON vst.dctr_id = dctr.dctr_id
          LEFT 
          JOIN accidents acc 
           ON acc.dctr_id = dctr.dctr_id
          LEFT 
          JOIN personell prsnl 
            ON prsnl.prsnl_id = vst.prsnl_id 
            OR prsnl.prsnl_id = acc.prsnl_id
         WHERE dctr.dctr_id = 60

I would like to get the following result:

dctr_id | dctr_name | visit_id | visit_date | acc_id | acc_date | prsnl_id | prsnl_name
-----------------------------------------------------------------------------------------
60      | Bezant    | 1        | 2018-12-31 | NULL   | NULL       | 79     | test_name2
60      | Bezant    | NULL     | NULL       | 51     | 2018-12-25 | 79     | test_name2
60      | Bezant    | NULL     | NULL       | 55     | 2018-12-20 | 86     | test_name1
join mysql
2 Answers

you need two sql queries with union. check my answer:

SELECT 
  dctr.dctr_id ,
  dctr.dctr_name ,
  null,
  null,
  acc.acc_id,
  acc.acc_date,
  prsnl.prsnl_id,
  prsnl.prsnl_name  
FROM doctor dctr
LEFT JOIN accidents acc ON acc.dctr_id = dctr.dctr_id
LEFT JOIN personell prsnl ON prsnl.prsnl_id = acc.prsnl_id
WHERE dctr.dctr_id = '60'
union
SELECT 
  dctr.dctr_id ,
  dctr.dctr_name ,
  vst.vist_id,
  vst.visit_date,
  null,null,
  prsnl.prsnl_id,
  prsnl.prsnl_name 
FROM doctor dctr
LEFT JOIN visits vst ON vst.dctr_id = dctr.dctr_id
LEFT JOIN personell prsnl ON prsnl.prsnl_id = vst.prsnl_id
WHERE dctr.dctr_id = '60';

Here is Fiddle Link

PS I used Oracle DB in my Fiddle but the SQL uses ANSI syntax. Dont worry about it.

user_11189780

To get results from visits or accidents, you need to UNION those two tables together, selecting NULL values for columns for which there is no corresponding data in that table (for example, acc_id in visits). Those results can then be JOINed to the doctor and personell tables to get the relevant doctor and person information for each visit/accident:

SELECT d.dctr_id ,d.dctr_name ,i.visit_id,i.visit_date,i.acc_id,i.acc_date,p.prsnl_id,p.prsnl_name
FROM doctor d
LEFT JOIN (SELECT dctr_id, visit_id, prsnl_id, visit_date, NULL AS acc_id, NULL AS acc_date
           FROM visits
           UNION
           SELECT dctr_id, NULL, prsnl_id, NULL, acc_id, acc_date
           FROM accidents) i
  ON i.dctr_id = d.dctr_id
LEFT JOIN personell p ON p.prsnl_id = i.prsnl_id
WHERE d.dctr_id = 60
ORDER BY i.visit_id, i.acc_id

Output:

dctr_id     dctr_name   visit_id    visit_date  acc_id  acc_date    prsnl_id    prsnl_name
60          Bezant      1           2018-12-31  (null)  (null)      79          test_name2
60          Bezant      (null)      (null)      51      2018-12-25  79          test_name2
60          Bezant      (null)      (null)      55      2018-12-20  86          test_name

Demo on SQLFiddle

user_94737640
Related