MYSQL Query multiple id to name resolution between two tables

Question

I have 2 tables like this:
[games]
gameid
hometeamid
awayteamid
score

and

[teams]
teamid
teamname

How would i create a query to output something like:
[home team][away team][score]
49ers chargers 28-17

You see, i need to resolve 2 team names with 2 team ids within the same table and output just the names. Thanks in advance for your help!

Answer 1

This should work for you:

select t1.teamname, t2.teamname, g.score
from games as g
     left outer join team as home_team
       on g.hometeamid = home_team.id
     left outer join team as away_team
       on g.awayteamid = away_team.id

Answer 2

SELECT (SELECT teams.teamname FROM teams WHERE teams.teamid=games.awayteamid) AS awayteam,     
(SELECT teams.teamname FROM teams WHERE teams.teamid=games.hometeamId) AS hometeam, score 
FROM games 
WHERE gameid = '1'

Answer 3

SELECT
  ht.TeamName AS HomeTeam,
  vt.TeamName AS AwayTeam,
  g.Score
FROM
  games g INNER JOIN teams ht
    on g.hometeamid = ht.teamid
  INNER JOIN teams vt
    on g.awayteamid = vt.teamid

I'd suggest naming the tables "game" and "team" - as I'm not a fan of plural table names. I'm not alone in this opinion, but it's really a style/preference thing.

Answer 4

Thanks for the quick responses, codemonkey this is exactly what i needed. Thanks all!