Find the count of categories from a table

Question

I am trying to develop an enquiry management system for an institute. I have 3 tables in mysql db.

1. leads_course_category table keeps course categories and has the columns: category_id, category_name, category_created_time, category_deleted.

2. leads_course_details table stores courses under categories and has the columns: course_id, course_category_id, course_name.

3. leads_enquiry_details table stores enquiry details and has the columns: enquiry_id, enquiry_name, leads_course_details_course_id, enquiry_deleted

I need to find the number of enquiries for each category. I tried the following query:

SELECT category_name,COUNT(*) as COUNT
FROM leads_course_category
RIGHT JOIN   leads_course_details 
on category_id=leads_course_details.course_category_id  
RIGHT JOIN `leads_enquiry_details`   
on leads_course_details.course_id= leads_enquiry_details.leads_course_details_course_id  
WHERE leads_enquiry_details.enquiry_deleted=1 
GROUP BY leads_course_category.category_id

This query skips all the categories having null values, but I need to show that as count of zero.

Please help me to resolve this.

Answer 1

The condition enquiry_deleted = 1 must be moved to the ON clause:

select c.category_name, count(ed.enquiry_id) count 
from leads_course_category c 
left join leads_course_details cd on c.category_id = cd.course_category_id  
left join leads_enquiry_details ed on cd.course_id = ed.leads_course_details_course_id and ed.enquiry_deleted = 1 
group by c.category_id, c.category_name