SQL Problem

[Ronage]

Situation:

Access Database

Goal: Construct a SQL statement to join data from two tables.

Table1 is a schedule

Fields are like this:

Date | Time | Team1 | Team2 |Location etc.

Table2 has the Team Names:

Team_ID | TeamName

I am trying to write a SQL statement that will give me the complete schedule for all teams which includes the Team Name and team number for each of team1 and team2.

Right now the SQL looks like this but Access complains:

SELECT S.GameDate, S.GameTime, H.TeamName, V.TeamName
FROM SchGames S
LEFT JOIN Team H ON (H.TeamNumber = S.HomeTeamNumber)
LEFT JOIN Team V ON (V.TeamNumber = S.VisitingTeamNumber);

A sample file is attached if you want to look at it and here are two screenshots of the tables..

http://injectionmolds.com/misc/table_SchGames.gif
http://injectionmolds.com/misc/table_team.gif


TIA

[Thread1]

Try the following SQL statements :

1.)
SELECT A.GameDate, A.GameTime, A.TeamName AS VisitingTeam, C.TeamName AS HomeTeam
FROM (SELECT * FROM SchGames A LEFT OUTER JOIN Team B ON (A.VisitingTeamNumber = B.TeamNumber)) AS A " & _
LEFT OUTER JOIN Team C ON (A.HomeTeamNumber = C.TeamNumber)

2.)
SELECT A.GameDate, A.GameTime, (SELECT TOP 1 TeamName FROM Team WHERE TeamNumber = A.VisitingTeamNumber) As VisitingTeam,
(SELECT TOP 1 TeamName FROM Team WHERE TeamNumber = A.HomeTeamNumber) As HomeTeam
FROM SchGames A"

[antares686]

You have to wrap the logic to seperate joins for the parser like so.


SELECT S.GameDate, S.GameTime, H.TeamName, V.TeamName
FROM (SchGames S
LEFT JOIN Team H ON H.TeamNumber = S.HomeTeamNumber)
LEFT JOIN Team V ON V.TeamNumber = S.VisitingTeamNumber;


also are you sure this should not be an INNER JOIN instead.

[M Owen]

antares686,

You are correct. By all rights it should be inner joins ... Obviously there shouldn't be a schedule record with either team ID not existing in the Team table ...

[Thread1]

That's even better!