Database Reference
In-Depth Information
| 1959 | Alaska | 1959-01-03 | Hawaii | 1959-08-21 |
+------+----------------+------------+----------------+------------+
For self-join problems of the “Which values are not matched by other rows in the
table?” variety, use a LEFT JOIN rather than an INNER JOIN . An instance of this is the
question “Which states did not join the Union in the same year as any other state?” In
this case, the solution uses a LEFT JOIN of the states table to itself:
mysql> SELECT s1.name, s1.statehood
-> FROM states AS s1 LEFT JOIN states AS s2
-> ON YEAR(s1.statehood) = YEAR(s2.statehood) AND s1.name <> s2.name
-> WHERE s2.name IS NULL
-> ORDER BY s1.name;
+----------------+------------+
| name | statehood |
+----------------+------------+
| Alabama | 1819-12-14 |
| Arkansas | 1836-06-15 |
| California | 1850-09-09 |
| Colorado | 1876-08-01 |
| Illinois | 1818-12-03 |
| Indiana | 1816-12-11 |
| Iowa | 1846-12-28 |
| Kansas | 1861-01-29 |
| Kentucky | 1792-06-01 |
| Tennessee | 1796-06-01 |
| Utah | 1896-01-04 |
| Vermont | 1791-03-04 |
| West Virginia | 1863-06-20 |
| Wisconsin | 1848-05-29 |
+----------------+------------+
For each row in the states table, the statement selects rows for which the state has a
statehood value in the same year, not including that state itself. For rows having no
such match, the LEFT JOIN forces the output to contain a row anyway, with all the s2
columns set to NULL . Those rows identify the states with no other state that joined the
Union in the same year.
14.5. Producing Master-Detail Lists and Summaries
Problem
Two tables have a master-detail relationship, and you want to produce a list that shows
each master row with its detail rows or a list that produces a summary of the detail rows
for each master row.
Search WWH ::




Custom Search