MySQL ODBC Connector - getting COUNT() TO return 0 for null matches in a query, how?

Author

getting COUNT() TO return 0 for null matches in a query, how?

Ferindo Middleton Jr

2006-02-28, 8:28 pm

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_att
endance
WHERE registration_and_att
endance.schedule_id = schedules.id
GROUP BY schedules.id ORDER BY start_date

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Michael Stassen

2006-02-28, 8:28 pm

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_att
endance WHERE
> registration_and_att
endance.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_att
endance 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_att
endance. 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_att
endance, you get a
result row with NULLs for each selected column in registration_and_att
endance
(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_att
endance.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_att
endance 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/mysql? unsub...sie.nctu.edu.tw

Sponsored Links