[SOLVED] query help

[hvle]

hi guys,

 

couldn't think of a single query that do this:

 

table 'team'

teamID    teamName

1            Sharks

2            Red Wings

 

table 'game'

gameId  homeTeamId    awayTeamId

100              1                    2

 

I want a query that return 1 row like this

matchId      homeTeamId    awayTeamId    homeTeamName      awayTeamName

100                  1                  2                    Sharks                Red Wings

 

Any idea?

 

 

[hvle]

 

select t1.idn as matchId,t1.homeTeamId,t1.awayTeamid,t2.teamName as homeTeamName, t3.teamName as awayTeamName from `game` t1 join
`team` t2 on t1.homeTeamId=t2.teamId join `team` t3 on t1.awayTeamId=t3.teamId