select concat(lookup_hospital.city_code, '/', lookup_hospital.hospital_code) as hospital_code, club.name, lookup_club_type.name, count(*) as patient_count from tracking_infant

left join club_patient on club_patient.id_patient = tracking_infant.id_patient
left join club on club.id = club_patient.id_club 
left join lookup_hospital on lookup_hospital.id = club.id_hospital
left join lookup_club_type on club.club_type = lookup_club_type.id
where tracking_infant.id_patient in (select id_patient from club_patient)
group by club.id