join on 2 fields?

  • 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??

     

     

  • 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