You want to display a relationship between tables when records in either table may be matched by multiple records in the other table.
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.
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
abbrev columns that
list full state names and their corresponding abbreviations:
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
state column indicating the state in
which each painting was purchased. With no mapping,
painting entries can be displayed like this:
SELECT title, state FROM painting ORDER BY state;+-------------------+-------+ | title ...