I have a table ("Teams") that stores team names in a field called "Team". Then I have another table ("Games"), which includes, among other fields, "HTID", which is the home team ID, and "VTID", which is the visiting team ID. Each of those fields (HTID and VTID) corresponds to the ID field in the Teams table.

I want to be able to pull the data from the "Games" table and print something like this:

Marauders at Maulers
Destroyers at Devastators

But I can't figure out how to get a join to work in this case, since I need to pull the same field ("Team") twice from the Teams table, once for HTID and once for WTID.

That is, I can't do "SELECT Team AS HomeTeam, Team AS RoadTeam...", or if it's possible I can't figure out how to get it to work. I can normally do joins no problem, but this is a bit different.

Any ideas out there? Thanks!