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