Multiple Queries using SQL

[John Holmes]

I'm trying to design a query to create a record od football/soccer
scores and have two main tables. The tables are as follows

TEAMS
Id The primary key
Team The name of the team


FIXTURES
Id The primary key
Date Date of the match
Home The home team's Id on Teams
Away The away team's Id on Teams
Homegoals
Awaygoals

I created an initial query to obtain the home team's name using

SELECT Fixtures.*, Teams.Team
FROM Teams LEFT JOIN Fixtures ON Fixtures.Home = Teams.Id;

However, I cannot see how to extend it to generate the away team's name
as well.

Could you advise me please.

 

[John Viescas]

Why are you using an outer join? You can get the second team by including a
second copy of the TEAMS table in your query:

SELECT Fixtures.*, Teams.Team As HomeTeam, Teams_1.Team As AwayTeam
FROM (Teams
INNER JOIN Fixtures
ON Fixtures.Home = Teams.Id)
INNER JOIN Teams As Teams_1
ON Fixtures.Away = Teams_1.ID


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[John Holmes]

Why are you using an outer join? You can get the second team by
including a second copy of the TEAMS table in your query:

SELECT Fixtures.*, Teams.Team As HomeTeam, Teams_1.Team As AwayTeam
FROM (Teams
INNER JOIN Fixtures
ON Fixtures.Home = Teams.Id)
INNER JOIN Teams As Teams_1
ON Fixtures.Away = Teams_1.ID

Not sure if my original response disappeared into the blue nowhere so
I'll re-post.

Thanks for the advice - it worked perfectly. I'll try to find out more
about Joins. Is it ethical to ask if any of the books in your signature
would be appropriate?

 

[John Viescas]

John-

We wrote the SQL book based on the ANSI standard, but we were able to
provide most of the sample queries in Access 2000 databases. One of the
sample databases is for a bowling league, so you'll find the answer to your
"home" and "away" team problem there as well as many others that you might
find interesting.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)