Left join on 3 tables?

Question

I have to use LEFT JOIN on 3 tables: UNITS, ROOMS_CHECK_IN and COMMENTS. Basically I want to show UNITS and for each unit count of rooms check in and count of comment. But I am getting same 4 digit number when I am running for rooms check in and comment count. If I separate the 2 queries with single left join, it works fine.

Below is the query:

SELECT u.ID, 
       u.unit_name,
       count(c.checkin_status) as total_chekin ,
       count(com.ID) as total_comment ,
       h.hospital_name
FROM HOSPITALS h,  UNITS u 
LEFT OUTER JOIN ROOMS_CHECK_IN c ON c.unit_id = u.ID  AND c.room_status=0  
LEFT OUTER JOIN COMMENTS com  ON com.unit_id = u.ID  
WHERE  h.ID = u.hospital_id AND  u.hospital_id=3 
GROUP BY  u.ID;

Kindly help.

Promote the use of explict `JOIN` sintaxis, Aaron Bertrand wrote a nice article [Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) about it. — Juan Carlos Oropeza, Oct 20 '16 at 02:50

score 2 · Accepted Answer · answered Oct 20 '16 at 02:50

2

Never use commas in the FROM clause. Always use explicit proper JOIN context.

Then, you probably want count(distinct) (or to aggregate before joins):

SELECT u.ID, u.unit_name,
       count(distinct c.checkin_status) as total_chekin,
       count(distinct com.ID) as total_comment,
       h.hospital_name
FROM HOSPITALS h JOIN
     UNITS u 
     ON h.ID = u.hospital_id LEFT OUTER JOIN
     ROOMS_CHECK_IN c
     ON c.unit_id = u.ID AND c.room_status = 0 LEFT OUTER JOIN
     COMMENTS com
     ON com.unit_id = u.ID  
WHERE u.hospital_id = 3
GROUP BY u.ID, u.unit_name, h.hospital_name;

answered Oct 20 '16 at 02:50

Gordon Linoff

1,242,037
58
646
786

Why you think `DISTINCT` is relevant? I think only add the `unit_name` and `hostpital_name` on the group by will be enough – Juan Carlos Oropeza Oct 20 '16 at 02:55
@JuanCarlosOropeza . . . `count()` counts the number of non-NULL values. That is likely to be the same. I think the OP wants distinct counts along each dimension, when the `join`s are producing Cartesian products. – Gordon Linoff Oct 20 '16 at 02:57
Thank you so much for your answer, yes without DISTINCT I am getting Cartesian products. – maddie Oct 20 '16 at 04:31

Left join on 3 tables?

1 Answers1