MySQL Cookbook, 2nd Edition

Enumerating a Many-to-Many Relationship

Problem

You want to display a relationship between tables when rows in either table might be matched by multiple rows in the other table.

Solution

This is a many-to-many relationship. It requires a third table for associating your two primary tables and a three-way join to list the correspondences between them.

Discussion

The artist and painting tables used in earlier sections are related in a one-to-many relationship: a given artist may have produced many paintings, but each painting was created by only one artist. One-to-many relationships are relatively simple and the two tables in the relationship can be joined with a key that is common to both tables.

Even simpler is the one-to-one relationship, which often is used to perform lookups that map one set of values to another. For example, the states table contains name and abbrev columns that list full state names and their corresponding abbreviations:

mysql>SELECT name, abbrev FROM states;
+----------------+--------+
| name           | abbrev |
+----------------+--------+
| Alabama        | AL     |
| Alaska         | AK     |
| Arizona        | AZ     |
| Arkansas       | AR     |
...

This one-to-one relationship can be used to map state name abbreviations in the painting table, which contains a state column indicating the state in which each painting was purchased. With no mapping, painting entries can be displayed like this:

mysql>SELECT title, state FROM painting ORDER BY state; +-------------------+-------+ | title | state | +-------------------+-------+ ...