Getting multiple values from a lookup table with MySQL

[buzzed_man]

Hi all,

I have a Database for tracking team games. One table has fields :

GAME_ID, home_team_ID,away_team_ID,winner_team_ID

And my lookup table has fields :

team_ID,Team_Name

I want to create a query that will return the GAME_ID and home and away team NAMES, but how do you get SQL to lookup multiple values in the lookup table ? I can only return a single team name ???

Buzz

[Lars_Berg]

select l1.team_name, l2.team_name
from lookup l1 inner join games
on l1.team_id = home_team_id
inner join lookup l2
on away_team_id = l2.team_id