Post reply

Need Help With Query

  • May 9, 2004 at 8:45 pm

    #85506

    I have the following 2 tables:

    TABLE: Game

    GameID, Team1ID, Team2ID

    TABLE: TeamList

    TeamListID, TeamName

     

    I'd like to retrieve the "TeamName" for both "Team1ID" and "Team2ID" in the same query if possible.  I've been using 2 queries to retrieve this info (see below).  Is there any way to retrieve this info in 1 query?  I'd love to minimize the # of calls to the database.  Thanks in advance for any help.

    --QUERY1: Team1 Name

    SELECT TeamName FROM TeamList tl JOIN Game g ON tl.TeamListID = g.Team1ID

    --QUERY2: Team2 Name

    SELECT TeamName FROM TeamList tl JOIN Game g ON tl.TeamListID = g.Team2ID

     

  • May 9, 2004 at 10:53 pm

    #505743

    You need to use aliases ... effectively creating two instances of the TeamList table and then using them as if you had two separate (but identical) tables ...

    select team1.teamname, team2.teamname

    from game g

    inner join Teamlist team1 on g.Team1ID = team1.TeamListID

    inner join Teamlist team2 on g.Team2ID = team2.TeamListID

    haven't tested this, but should be pretty close

  • May 10, 2004 at 11:48 am

    #505849

    It worked - many thanks!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply