MS ACCESS SQL Query on two tables

Question

I am new to SQL and am having trouble setting up this query. I have two tables, one which holds info about the teams, named TEAMS which looks like this:

TEAMS

Name|City|Attendance
Jets|  NY| 50
...

And the other which holds info about the games played, named GAMES:

GAMES

Home|Visitors|Date     |Result
Jets| Broncos| 1/1/2012| Tie
...

For each row in GAMES I am to give the name and city of each team (both home team and visiting team), the date and result of the game--a total of six fields.

Right now I have figured out how to display the fields but only in a way that gives one team's city, not both. Here is my query so far. I figure I need a subquery of some sort but can't figure out how to set it up. Thanks for the help.

SELECT Home, City AS [Home City], Visitors, City AS [Visitor City], Date, Result
FROM GAMES, TEAMS
WHERE Home=Name
ORDER BY Home DESC , Result DESC , Visitors DESC;

Answer 1

You'll need to use the TEAMS table twice (and, while on it, instead of using implicit joins in the where condition, use explicit joins.. cleaner and easier to read):

select g.home, ht.city as home_city, 
       g.visitor, vt.city as visitor_city, 
       g.date, g.result
from (games as g
     inner join teams as ht on g.home = ht.name)
     inner join teams as vt on g.visitor = vt.name

I'm using aliases in this example for the tables to shorten the table names (g for GAMES, ht for "home team" and vt for "visitor team"). Notice that, instead of defining the relation conditions in the where clause, I'm defining it directly in the from clause (using inner join).

Answer 2

You will need to JOIN the TEAMS table twice.

SELECT Home, HomeTeam.City as Home_City, Visitors, VisTeam.City AS Vis_City,
  -- more field
FROM GAMES JOIN TEAMS AS HomeTeam
ON GAMES.Home=HomeTeam.Name
   JOIN TEAMS AS VisTeam ON GAMES.Visitors=VisTeam.Name;