0

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.

user3783243
  • 5,368
  • 5
  • 22
  • 41
RatheeshTS
  • 411
  • 3
  • 15
  • 1
    Right joins are almost never used - it's not incorrect to use them but in most cases left joins are much more understandable. – P.Salmon Dec 31 '19 at 13:38
  • please see https://stackoverflow.com/tags/sql/info on how to improve your question. If you can provide a schema to replicate the problem it is easier for others to help. – JohnC Dec 31 '19 at 13:57

1 Answers1

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
forpas
  • 160,666
  • 10
  • 38
  • 76