O'Reilly logo

MySQL Cookbook by Paul DuBois

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 record with its detail records, or a list that summarizes the detail records for each master record.

Solution

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 records for which some detail record exists, use a regular join based on the primary key in the master table. To produce a list that includes entries for all master records, even those that have no detail records, use a LEFT 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 record in one table might be matched by several records in the other. This section shows 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 join that matches each painting record to its corresponding artist record based on the artist ID values:

mysql> SELECT artist.name, painting.title
    -> FROM artist, painting WHERE artist.a_id = painting.a_id
    -> ORDER BY 1, 2; +----------+-------------------+ | name | title | +----------+-------------------+ | Da Vinci | The Last Supper | | Da Vinci | The Mona Lisa | | Renoir | Les Deux ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required