Join One Table to 2 Columns?

[Hutts]

I have an online statistical application for a local softball league. 2 of the tables are set up as follows
[tt]
TABLE: TEAMS
ID NAME DIVISION_ID
-- -------- -----------
1 Team 1 1
2 Team 2 1

TABLE: SCHEDULE
GAME_ID TEAM1_ID TEAM_2ID DATE TIME
------- -------- -------- ------ -------
1 1 2 6/5/06 7:00 PM

[/tt]
How can you create a table join in an sql query of SCHEDULE so that team1_ID and team2_ID refers to the team name for both columns to avoid doing 2 additional queries to get the team names from their ids.

 

[PHV]

A starting point:
SELECT S.*, T1.NAME Team1, T2.NAME Team2
FROM SCHEDULE S
INNER JOIN TEAMS T1 ON S.TEAM1_ID = T1.ID
INNER JOIN TEAMS T2 ON S.TEAM2_ID = T2.ID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886