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.

DJ. · Accepted Answer · 2010-02-21 23:45:41Z

1

What you are looking for is standard inner join.

answered Feb 21, 2010 at 23:45

DJ.

6,7842 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joel · Accepted Answer · 2010-02-22 00:16:32Z

0

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

edited Feb 22, 2010 at 0:16

answered Feb 21, 2010 at 23:57

Joel

19.4k3 gold badges67 silver badges84 bronze badges

2 Comments

Tyler Rash Over a year ago

I wish 1. I were still young enough to worry about homework problems 2. I had actually taken a class that taught the basics of SQL. Thanks for the assistance.

Joel Over a year ago

I'm just being careful because it sounded like a homework problem. I'll add the rest of the code.

Collectives™ on Stack Overflow

Help with MySQL SELECT statement, multiple tables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related