maddie maddie - 1 month ago 5
MySQL Question

Left join on 3 tables?

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.

Answer

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;
Comments