How do I JOIN when 2 separate items reside in the same table?

[Barney the Rubble]

I'm racking my brain and I cant for the life of me figure this out.
It's probably simple for you SQL Gurus, so could you please take a look
and help?
I have a Team Schedule database is Access with 2 tables.
The 'Schedule' table looks like this:
ID, GameDate, VisitorID, HomeID

My 'Team' table looks like this:
ID, TeamName

Now the problem is that I want to run a query and display the Schedule
as it should look:
GameDate Visitor Home
10/4/2006 Team1 Team2
10/4/2006 Team3 Team4

But I cant figure out the JOIN query. The only one I can do is:
SELECT Schedule.GameDate, Team.TeamName AS Visitor, Team.TeamName AS
Home
FROM Schedule INNER JOIN Team ON Schedule.VisitorID=Team.ID;

But as you can see, it only uses one column for team names and provides
results like:
GameDate Visitor Home
10/4/2006 Team1 Team1
10/4/2006 Team3 Team3

PLEEEEEASE, can someone smart help me out. This is driving me up the
wall because I know it should be sooooooooo simple.

Thanks in Advance

 

[Granny Spitz via AccessMonster.com]

I want to run a query and display the Schedule
as it should look:
GameDate Visitor Home
10/4/2006 Team1 Team2
10/4/2006 Team3 Team4

You can do it with correlated subqueries. One way is like this:

SELECT GameDate, (SELECT TeamName FROM Team WHERE ID = VisitorID) AS Visitor,
(SELECT TeamName FROM Team WHERE ID = HomeID) AS Home
FROM Schedule;

This is driving me up the
wall because I know it should be sooooooooo simple.

This time it was hard, but next time you do it it'll be familiar, and it will
be soooooooo simple!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You just create 2 versions of the Team table: one for the home team, and
one for the visitor team.

SELECT S.GameDate, V.TeamName AS Visitor, H.TeamName AS Home
FROM (Schedule AS S INNER JOIN Team AS V ON S.VisitorID=V.ID) INNER JOIN
Team AS H ON S.HomeID=H.ID
ORDER BY GameDate
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUF3m4echKqOuFEgEQL5bwCfXqi1ODnOxp4KlyE4Rr9CLjsRd5MAoNdb
bwSyPI0eZKcWHYIypJzEYc9o
=fL9Z
-----END PGP SIGNATURE-----