August 7, 2007 at 2:54 pm
i have 2 tables as follows:
tblSchedule
GameID
date
hometeamID
awayTeamID
tblTeams
id
TeamName
i would like to run a query that would return all the scheduled games from tblSchedule and include the Teamnames(from tblTeams) in addition to the teamID for each game in the query results.
so for example the results would look somethgin like this
1, 9/6/2007, 4, Denver Broncos, 18, San Francisco 49ers
2, 9/9/2007, 6, Green Bay Packers, 24, Raiders
so normally if it was just ohne team in each game i woudl just do somethgin like this:
SELECT tblSchedule.*, tblTeams.teamName
FROM tblSchedule
INNER JOIN teams
ON schedule.HomeTeamID = tblTeams.id
but since i want to join on tblSchedule.HomeTeamID = tblTeams.id AND on tblSchedule.AwayTeamID = tblTeams.id
how would that work??
August 7, 2007 at 4:37 pm
Just join to the teams a second time using the AwayTeamID in the join. You will need to alias the table to tell them apart.
SELECT tblSchedule.*, HomeTeam.teamName as HomeTeam, AwayTeam.TeamName AS AwayTeam
FROM tblSchedule
INNER JOIN tblTeams AS HomeTeam
ON tblSchedule.HomeTeamID = HomeTeam.id
INNER JOIN tblTeams AS AwayTeam
ON tblSchedule.AwayTeamID = AwayTeam.ID
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply