MySQL Cookbook, 2nd Edition

Using a Join to Fill or Identify Holes in a List

Problem

You want to produce a summary for each of several categories, but some of them are not represented in the data to be summarized. Consequently, the summary has missing categories.

Solution

Create a reference table that lists each category and produce the summary based on a LEFT JOIN between the list and the table containing your data. Then every category in the reference table will appear in the result, even those not present in the data to be summarized.

Discussion

When you run a summary query, normally it produces entries only for the values that are actually present in the data. Let’s say you want to produce a time-of-day summary for the rows in the mail table. That table looks like this:

mysql>SELECT * FROM mail;
+---------------------+---------+---------+---------+---------+---------+
| t                   | srcuser | srchost | dstuser | dsthost | size    |
+---------------------+---------+---------+---------+---------+---------+
| 2006-05-11 10:15:08 | barb    | saturn  | tricia  | mars    |   58274 |
| 2006-05-12 12:48:13 | tricia  | mars    | gene    | venus   |  194925 |
| 2006-05-12 15:02:49 | phil    | mars    | phil    | saturn  |    1048 |
| 2006-05-13 13:59:18 | barb    | saturn  | tricia  | venus   |     271 |
| 2006-05-14 09:31:37 | gene    | venus   | barb    | mars    |    2291 |
| 2006-05-14 11:52:17 | phil    | mars    | tricia  | saturn  |    5781 |
...

To determine how many messages were sent for each hour of the day, use the following statement:

mysql>SELECT HOUR(t) AS hour, COUNT(HOUR(t)) AS count ...