CODEWITHSUNDEEP
mysqljoinleft-join
RajSanpui
RajSanpui

Reputation: 12064

MySQL query left join messing things up

This query returns 4 rows:

SELECT count(*) from 
patients join patient_observations on patients.patient_id=patient_observations.patient_id
join studies on patient_observations.study_id=studies.study_id AND studies.patient_id=patients.patient_id
join series on series.study_id=studies.study_id 
join instances on instances.series_id=series.series_id
join sop_classes on sop_classes.sop_class_id=instances.sop_class_id
join files on files.instance_id=instances.instance_id
join modalities on modalities.modality_id=series.modality_id
AND patient_name LIKE 'DRAGON^DEBBIE%';

If i add this extra line, it returns 3923 rows which is wrong:

left join issuers on patients.issuer_of_patient_identifier=issuers.issuer_id

Wrong in the sense, issuers table has just 121 rows:

mysql> select count(*) from issuers;
+----------+
| count(*) |
+----------+
|      121 |
+----------+
1 row in set (0.00 sec)

The purpose of adding left join was, there might be some patients where patients.issuer_of_patient_identifier is NULL as if i just do a normal join it returns 0 rows (See this: MySQL Matching where clause with optional NULL)

Please help, so that i can get the correct rows (Only 4)

Upvotes: 0

Views: 47

Answers (1)

shakhawat
shakhawat

Reputation: 2727

A
------
1 a1
2 a2

B
-------
1 b1 a1
2 b2 a1



left join A and B
----------------------

1 a1 1 b1 a1
1 a1 2 b2 a1
2 a2 null null null
2 a2 null null null

Here are 4 rows as expected, and if you count(*) you will get 4 rows. But if you want to know the matching number of rows in table B, you should do count(distinct b.id)

summary: try count(distinct issuer.issuer_id) instead of count(*)

After discussion in chat:

Actually the big problem was in the filtering clause. The sql should be like below -

SELECT count(*) from 
patients join patient_observations on patients.patient_id=patient_observations.patient_id 
join studies on patient_observations.study_id=studies.study_id AND studies.patient_id=patients.patient_id 
join series on series.study_id=studies.study_id 
join instances on instances.series_id=series.series_id 
join sop_classes on sop_classes.sop_class_id=instances.sop_class_id 
join files on files.instance_id=instances.instance_id 
left join modalities on modalities.modality_id=series.modality_id 
left join issuers on (patients.issuer_of_patient_identifier=issuers.issuer_id) 
where patient_name LIKE 'DRAGON^DEBBIE%';

Upvotes: 1

Related Questions