Ferindo Middleton Jr wrote:
I have the following query which counts the records from a table called
registration that have an schedule_id that matches a record in another
table called schedules. The below query works fine but how can I get it
to return a COUNT() of 0 each instance where there is no record in the
registration table that matches a schedules.id record?
SELECT schedules.id, schedules.start_date, schedules.end_date,
COUNT(schedules.id) FROM schedules,
registration_and_attendance WHERE
registration_and_attendance.schedule_id = schedules.id
GROUP BY schedules.id ORDER BY start_date
Your query, rewritten to use an explicit join (with the join condition in the ON
clause, where it belongs, rather than in the WHERE clause) and table aliases:
SELECT s.id, s.start_date, s.end_date,
COUNT(s.id)
FROM schedules s
JOIN registration_and_attendance ra ON ra.schedule_id = s.id
GROUP BY s.id
ORDER BY s.start_date;
This query finds only rows from schedules that have matching entries in
registration_and_attendance. As you have seen, you can't count what isn't there.
If you change the JOIN to a LEFT JOIN, however, you are guaranteed to get an
output row for every single id in schedules (the table on the left). For each
schedules.id that is not present in registration_and_attendance, you get a
result row with NULLs for each selected column in registration_and_attendance
(the table on the right).
Next, we need to take advantage of the fact that count(field) only counts
non-NULL values of field. The key is to count something in the table on the
right, say registration_and_attendance.schedule_id, because it will be NULL (and
have a 0 count) when there are no matches.
Thus, I believe the query you want is
SELECT s.id, s.start_date, s.end_date,
COUNT(ra.schedule_id)
FROM schedules s
LEFT JOIN registration_and_attendance ra ON ra.schedule_id = s.id
GROUP BY s.id
ORDER BY s.start_date;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
