Help with MySQL SELECT statement, multiple tables

Question

I have the following tables:

reg_season

+--------+--------+---------+------+--------+
| season | league | team    | wins | losses |
+--------+--------+---------+------+--------+
| 1962   | AL     | Yankees | 96   | 66     |
+--------+--------+---------+------+--------+

postseason

+--------+---------+----------+
| season | team    | finish   |
+--------+---------+----------+
| 1962   | Yankees | champion |
+--------+---------+----------+

mvp

+--------+--------+--------+
| season | league | winner |
+--------+--------+--------+
| 1962   | AL     | Mantle |
+--------+--------+--------+

rookie_of_the_year

+--------+--------+--------+
| season | league | winner |
+--------+--------+--------+
| 1962   | AL     | Tresh  |
+--------+--------+--------+

Each table contains a single entry for each season.

I need a query that will produce summary information for each season, ie:

+--------+--------+------------------+--------------------+--------+-------+
| season | league | reg_season_champ | world_series_champ | mvp    | roy   |
+--------+--------+------------------+--------------------+--------+-------+
| 1962   | AL     | Yankees          | Yankees            | Mantle | Tresh |
+--------+--------+------------------+--------------------+--------+-------+
| 1961   | ...    | ...              | ...                | ...    | ...   |
+--------+--------+------------------+--------------------+--------+-------+
| 1960   | ...    | ...              | ...                | ...    | ...   |
+--------+--------+------------------+--------------------+--------+-------+

Please point me in the right direction. Thanks.

Answer 1

What you are looking for is standard inner join.

Answer 2

Select r.season, r.league, r.team as reg_season_champ, p.team as world_series_champ, m.winner as mvp, y.winner as roy from reg_season r inner join postseason p on r.season = p.season and r.league = p.league and p.finish = 'champion' inner join mvp m on r.season = m.season and r.league = m.league inner join rookie_of_the_year y on r.season = y.season and r.league = y.league