MySQL data fetching

Question

I have trouble to understand how to fetch data from MySQL. Answer might by simple, but i'm stuck, and can't find any help over internet...

I have three tables, let's say first table is named series, second is teams and third id games. Table structure is something like this:

series:
id
name

teams:
id
name

games:
series_id (relates to series.id)
hometeam_id (relates to teams.id)
visitorteam_id (relates to teams.id)

So my problem is fetching rows from games, where those id's need to be there names, not id's...

Result should be something like this:
"championship
wolverines
marines"

not like
"1
45
142"

Currently i'm fetching those series and teams tables to hashref (in perl) and taking id's from there. But there must be more efficient way to do that in one sql query instead of three.

Answer 1

You need to join the teams table twice (once for home team, second time for visitors)

SELECT g.*,h.name as hometeam_name,v.name as visitorteam_name FROM games g
  INNER JOIN series s ON g.series_id=s.id
  INNER JOIN teams h ON g.hometeam_id=h.id
  INNER JOIN teams v ON g.visitorteam_id=v.id