CODEWITHSUNDEEP
mysql
user2557228
user2557228

Reputation: 1

SQL lookup table with multiple IDs per row

I'm using the Retrosheet baseball play-by-play database to help me learn SQL (using mySQL). It has a table called games, which gives general information about all the games played: date, stadium, teams, score, etc. It also gives information about players, like starting pitchers, lineups, umpires, etc. However, all people are labeled using IDs. I have a lookup table that matches all IDs to first and last names.

If I wanted to select * from the games database matching a specific game, but I wanted to return the names from the ID table instead of the IDs, is there a way to do that?

To be more specific, lets say I wanted to select game_ID, home_team_id, away_team_id, home_start_pit_id, away_start_pit_id from games. What I would like to have is both pitcher's real names instead of id.

Upvotes: 0

Views: 922

Answers (1)

Brian Hoover
Brian Hoover

Reputation: 7991

Assuming there is a table called "pitchers" with fields id, first_name, last_name (and others)

You would do a simple join.

select game_id, home_team_id,home_team_id, away_team_id,
   home_team_pitcher.first_name, home_team_pitcher.last_name,
   away_team_pitcher.first_name, away_team_pitcher.last_name
   from games
   join pitchers as home_team_pitcher on pitchers.id = home_start_pit_id
   join pitchers as away_team_pitcher on pitcher.id = away_start_pit_id

Upvotes: 3

Related Questions