INNER JOIN 2 entries same table

[run_GMoney]

OK, i've got 2 tables. One table (GAME) has a game_id, away_team_id, and home_team_id. The other table (TEAM)has a team_id and a teamname. The home_team_id from the GAME table matches a team_id from the TEAM table and the away_team_id should match a team_id also. I want to create a query that would display game_id, home_team_id, teamname, away_team_id and teamname. Is that clear what I'm asking for? I'm not even sure how to ask the question.

GAME.game_id, GAME.home_team_id, TEAM.teamname that matches the home_team_id, GAME.away_team_id, TEAM.teamname that matches the away_team_id.

I don't know. If anyone can decipher anything out of this I'd appreciate it.

[toma42]

alias teams by using

select....from teams t1, teams t2 where t1=ref_team1 and t2 = ref_team2

That isn't your exact solution but it will get you started. Note you can also alias columns.

[kevinsequeira]

select a.game_id, a.away_team_id, b.teamname, a.home_team_id, c.teamname
from GAME a, TEAM b, TEAM c
where a.away_team_id = b.team_id and a.home_team_id = c.team_id

reg
kevin

[run_GMoney]

Solid Kevin, that worked great. Thanks