MySQL Cookbook

Enumerating a Many-to-Many Relationship

Problem

You want to display a relationship between tables when records in either table may be matched by multiple records 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 related by means of a key that is common to both tables.

Even simpler is the one-to-one relationship, which often is used for performing 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 is a one-to-one relationship. It 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 ...