Joining 4 tables with good references to each other

Question

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

score 1 · Answer 1 · answered Jan 07 '19 at 22:59

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.

Thanks a lot! That fiddle is awesome. I'll make good use of it. If I had +15 rep I would vote up! — MarcM, Jan 08 '19 at 00:19

Nick · Accepted Answer · 2019-01-13T07:26:01.267

1

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

edited Jan 13 '19 at 07:26

answered Jan 07 '19 at 23:01

Nick

138,499
22
57
95

Thank you so very much. I wouldn't thought of this by myself. This works as a charm. If I had +15 rep I could vote up. – MarcM Jan 08 '19 at 00:16
@philipxy You are absolutely correct, my original wording was incorrect. I've reworded the answer - happy to take any further feedback on the new wording. – Nick Jan 12 '19 at 23:39
Hi. Explaining/justifiying why a query expression meets a specification requires a certain non-trivial presentation that generally isn't going to fit in an answer. It would be some part of [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) Textbooks don't even explain/justify, they just give examples & hope people get it. So when I comment it is generally just get answers to not say wrong things. – philipxy Jan 13 '19 at 01:17
Here now you don't exactly say anything wrong, but what you say is so unclear & vague that there's not much point in saying it. (But don't get me wrong, this is ubiquitous.) Eg in "get results from visits and accidents independent of each other", lots is hidden in the unexplained idiosyncratic "independent". (Ultimately any lucid & sound explanation *has* to at its foundation involve JOIN returning rows that satisfy the AND of two particular conditions & UNION returning rows that satisfy one OR another of particular conditions, plus the conditions satisfied in the bases & the query result.) – philipxy Jan 13 '19 at 01:36
@philipxy thanks for the feedback again. We could probably go round in circles for a while longer, or - if you're willing - you could edit the post with some better wording (which I could then learn from too)? – Nick Jan 13 '19 at 03:49
You have an inner join that eliminates non-null i.x following a left join that has i as right table. This means the left join could/should have been an inner join. Then observe that all your nulls are introduced by your union. And observe that outer joins return the union of an inner join & unmatched rows null-extended. So there's a version of this query where the nulls are instead introduced by full join or two left joins. Such an outer join version might be clearer from a native SQL perspective. (Hmm... The other answer?) (This comment arose independently from my other comments.) – philipxy Jan 13 '19 at 04:30
1

@MarcM You might be interested in the comments here. (Nb the question was edited after you accepted it & after an earlier deleted comment of mine criticizing "you can't JOIN them" in the accepted version.) – philipxy Jan 13 '19 at 04:31
@philipxy the inner join to personnel was a copying error which I have now corrected. Thanks for picking up on it. I've had another go at improving the explanation. – Nick Jan 13 '19 at 07:33
The original query which I used for asking the main question is a simplified version intended for a clear question. Upon deploying the answer I did not encounter any problems or errors but I will take a look again at the renewed answer. Thanks for taking this so serious. Oh, and about the humanly readable "hope people get it" part: I did. And if I didn't I would read it 10x or ask again :) – MarcM Jan 14 '19 at 15:46

Joining 4 tables with good references to each other

2 Answers2