select field twice in sql statement

[Anon]

I am trying to select the Team Names. I have a GameHomeTeamID and a GameVisitorTeamID In my ITGame table in the database.

The team names associated with those ID's are in another table in the database called ITTeam.

Could I somehow select the team names with one statement??

eg) something like:

$sql = "SELECT ITGame.GameHomeTeamID, ITGame.GameVisitorTeamID, ITTeam.TeamName, ITTeam.TeamName
FROM ITGame
LEFT JOIN ITTeam ON ITGame.GameHomeTeamID = ITTeam.TeamID AND ITGame.GameVisitorTeamID= ITTeam.TeamID.

I know this probably isn't right but does anyone have any idea how to do this??

Thanks sooooo much for you time.

[[deleted]]

You'd have to join with the second table twice, like this.

SELECT table1.hometeamid,
       table2.hometeamname,
       table1.visitorteamid,
       table3.visitorteamname
FROM   table1, table2, table3
WHERE  table1.hometeamid = table2.teamid
AND    table1.hometeamid = table3.teamid

[Anon]

You said:
SELECT table1.hometeamid,
table2.hometeamname,
table1.visitorteamid,
table3.visitorteamname
FROM table1, table2, table3
WHERE table1.hometeamid = table2.teamid
AND table1.hometeamid = table3.teamid

but it is more like this because their is no separate homeTeamName and visitorTeamName. It is just teamName field for both.
Like:

SELECT table1.hometeamid,
table2.teamname,
table1.visitorteamid,
table2.teamname
FROM table1, table2
WHERE table1.hometeamid = table2.teamid
AND table1.visitorteamid = table3.teamid

is that possible??

[[deleted]]

Sorry, my mistake.

the FROM line should read:

FROM table1, table2, table2 AS table3

so table3 becomes copy of table2,
so you can access the same table (the table with the team-names in it) twice in the same query.

[Anon]

cool thanks!!!