MySQL Cookbook, 2nd Edition

Producing Master-Detail Lists and Summaries

Problem

Two 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.

Solution

This 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.

Discussion

It’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 ...