Recipe 12.4. Producing Master-Detail Lists and Summaries
ProblemTwo related tables have a master-detail relationship, and you want to produce a list that shows each master row with its detail rows or a list that produces a summary of the detail rows for each master row. SolutionThis is a one-to-many relationship. The solution to this problem involves a join, but the type of join depends on the question you want answered. To produce a list containing only master rows for which some detail row exists, use an inner join based on the primary key in the master table. To produce a list that includes entries for all master rows, even those that have no detail rows, use an outer join. DiscussionIt's often useful to produce a list from two related tables. For tables that have a master-detail or parent-child relationship, a given row in one table might be matched by several rows in the other. This recipe suggests some questions of this type that you can ask (and answer), using the artist and painting tables from earlier in the chapter. One form of master-detail question for these tables is, "Which artist painted each painting?" This is a simple inner join that matches each painting row to its corresponding artist row based on the artist ID values: mysql> SELECT artist.name, painting.title -> FROM artist INNER JOIN painting ON artist.a_id = painting.a_id -> ORDER BY name, title; +----------+-------------------+ | name | title | +----------+-------------------+ | Da Vinci | The Last Supper | | Da Vinci | The Mona Lisa | | Renoir | Les Deux Soeurs | | Van Gogh | Starry Night | | Van Gogh | The Potato Eaters | | Van Gogh | The Rocks | +----------+-------------------+ An inner join suffices as long as you want to list only master rows that have detail rows. However, another form of master-detail question you can ask is, "Which paintings did each artist paint?" That question is similar but not quite identical. It will have a different answer if there are artists listed in the artist table that are not represented in the painting table, and the question requires a different statement to produce the proper answer. In that case, the join output should include rows in one table that have no match in the other. That's a form of "find the nonmatching rows" problem that requires an outer join (Section 12.2). Thus, to list each artist row, whether there are any painting rows for it, use a LEFT JOIN: mysql> SELECT artist.name, painting.title -> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id -> ORDER BY name, title; +----------+-------------------+ | name | title | +----------+-------------------+ | Da Vinci | The Last Supper | | Da Vinci | The Mona Lisa | | Monet | NULL | | Picasso | NULL | | Renoir | Les Deux Soeurs | | Van Gogh | Starry Night | | Van Gogh | The Potato Eaters | | Van Gogh | The Rocks | +----------+-------------------+ The rows in the result that have NULL in the title column correspond to artists that are listed in the artist table for whom you have no paintings. The same principles apply when producing summaries using master and detail tables. For example, to summarize your art collection by number of paintings per painter, you might ask, "How many paintings are there per artist in the painting table?" To find the answer based on artist ID, you can count up the paintings easily with this statement: mysql> SELECT a_id, COUNT(a_id) AS count FROM painting GROUP BY a_id; +------+-------+ | a_id | count | +------+-------+ | 1 | 2 | | 3 | 3 | | 5 | 1 | +------+-------+ Of course, that output is essentially meaningless unless you have all the artist ID numbers memorized. To display the artists by name rather than ID, join the painting table to the artist table: mysql> SELECT artist.name AS painter, COUNT(painting.a_id) AS count -> FROM artist INNER JOIN painting ON artist.a_id = painting.a_id -> GROUP BY artist.name; +----------+-------+ | painter | count | +----------+-------+ | Da Vinci | 2 | | Renoir | 1 | | Van Gogh | 3 | +----------+-------+ On the other hand, you might ask, "How many paintings did each artist paint?" This is the same question as the previous one (and the same statement answers it), as long as every artist in the artist table has at least one corresponding painting table row. But if you have artists in the artist table that are not yet represented by any paintings in your collection, they will not appear in the statement output. To produce a count-per-artist summary that includes even artists with no paintings in the painting table, use a LEFT JOIN: mysql> SELECT artist.name AS painter, COUNT(painting.a_id) AS count -> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id -> GROUP BY artist.name; +----------+-------+ | painter | count | +----------+-------+ | Da Vinci | 2 | | Monet | 0 | | Picasso | 0 | | Renoir | 1 | | Van Gogh | 3 | +----------+-------+ Beware of a subtle error that is easy to make when writing that kind of statement. Suppose that you write the COUNT( ) function slightly differently, like so: mysql> SELECT artist.name AS painter, COUNT(*) AS count -> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id -> GROUP BY artist.name; +----------+-------+ | painter | count | +----------+-------+ | Da Vinci | 2 | | Monet | 1 | | Picasso | 1 | | Renoir | 1 | | Van Gogh | 3 | +----------+-------+ Now every artist appears to have at least one painting. Why the difference? The cause of the problem is that the statement uses COUNT(*) rather than COUNT(painting.a_id). The way LEFT JOIN works for unmatched rows in the left table is that it generates a row with all the columns from the right table set to NULL. In the example, the right table is painting. The statement that uses COUNT(painting.a_id) works correctly, because COUNT( expr ) counts only non-NULL values. The statement that uses COUNT(*) works incorrectly because it counts rows, even those containing NULL that correspond to missing artists. LEFT JOIN is suitable for other types of summaries as well. To produce additional columns showing the total and average values of the paintings for each artist in the artist table, use this statement: mysql> SELECT artist.name AS painter, -> COUNT(painting.a_id) AS 'number of paintings', -> SUM(painting.price) AS 'total price', -> AVG(painting.price) AS 'average price' -> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id -> GROUP BY artist.name; +----------+---------------------+-------------+---------------+ | painter | number of paintings | total price | average price | +----------+---------------------+-------------+---------------+ | Da Vinci | 2 | 121 | 60.5000 | | Monet | 0 | NULL | NULL | | Picasso | 0 | NULL | NULL | | Renoir | 1 | 64 | 64.0000 | | Van Gogh | 3 | 148 | 49.3333 | +----------+---------------------+-------------+---------------+ Note that COUNT( ) is zero for artists that are not represented, but SUM( ) and AVG( ) are NULL. The latter two functions return NULL when applied to a set of values with no non-NULL values. To display a sum or average value of zero in that case, modify the statement to test the value of SUM( ) or AVG( ) with IFNULL( ): mysql> SELECT artist.name AS painter, -> COUNT(painting.a_id) AS 'number of paintings', -> IFNULL(SUM(painting.price),0) AS 'total price', -> IFNULL(AVG(painting.price),0) AS 'average price' -> FROM artist LEFT JOIN painting ON artist.a_id = painting.a_id -> GROUP BY artist.name; +----------+---------------------+-------------+---------------+ | painter | number of paintings | total price | average price | +----------+---------------------+-------------+---------------+ | Da Vinci | 2 | 121 | 60.5000 | | Monet | 0 | 0 | 0.0000 | | Picasso | 0 | 0 | 0.0000 | | Renoir | 1 | 64 | 64.0000 | | Van Gogh | 3 | 148 | 49.3333 | +----------+---------------------+-------------+---------------+ |
