Database Reference
In-Depth Information
will speed up query execution. Thus, the disadvantage of the subquery goes away and
you can use it freely without concern over whether to use the temporary table instead.
See Also
This section shows how to answer maximum-per-group questions by selecting sum‐
mary information into a temporary table and joining that table to the original one or
by using a subquery in the
FROM
clause. These techniques have application in many
contexts. One of them is calculation of team standings, where the standings for each
group of teams are determined by comparing each team in the group to the team with
the best record.
Recipe 15.12
discusses how to do this.
14.8. Using a Join to Fill or Identify Holes in a List
Problem
You want to produce a summary by category, but some categories are missing from the
data to be summarized. Consequently, the summary has missing categories as well.
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. Every category in the
reference table will appear in the result, even those not present in the data to be sum‐
marized.
Discussion
A summary query normally produces entries only for categories actually present in the
data. Suppose that you want to summarize the
driver_log
table (introduced in
Chap‐
ter 7
), to determine how many drivers were on the road each day. The table has these
rows:
mysql>
SELECT * FROM driver_log ORDER BY rec_id;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
| 2 | Suzi | 2014-07-29 | 391 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 5 | Ben | 2014-07-29 | 131 |
| 6 | Henry | 2014-07-26 | 115 |
| 7 | Suzi | 2014-08-02 | 502 |
| 8 | Henry | 2014-08-01 | 197 |
| 9 | Ben | 2014-08-02 | 79 |
