help with multipul refrence to the same table

[otuatail]

Hi I have a table of world cup matches. I have a field for the home team and another for the away team. The problem is both of these fields reference the same team table. I beleive you can have 2 copies of the same table as an alias. Don't know how this is done. Here is the query

 

select WorldCup.Date,WorldCup.Channel, venues.Name, teams.Name AS H,teams.Name AS A 
FROM WorldCup,venues,teams Home, teams Away WHERE venues.VenueID = WorldCup.Venue AND Home.TeamID = WorldCup.Home AND Away.TeamID = WorldCup.Away

 

TIA

 

DEsmond.

 

[otuatail]

Solved

select WorldCup.ID,WorldCup.Date,  Home.Name AS Home, Away.Name AS Away, venues.Name AS Venue, WorldCup.Channel

FROM WorldCup,venues,teams Home, teams Away

WHERE venues.VenueID = WorldCup.Venue AND Home.TeamID = WorldCup.Home AND Away.TeamID = WorldCup.Away