Jump to content


Photo

Yet another question on JOIN, please help


  • Please log in to reply
1 reply to this topic

#1 nathank

nathank
  • Members
  • Pip
  • Newbie
  • 2 posts

Posted 21 November 2005 - 07:39 PM

I have been reading up on joins, but I still am a bit unclear how I would write my query to do the following.

I have a table named teams
|  ID  |  Teamname        |
|  1    |  The Good Team |
|  2    |  The Bad Team   |

And a table named matchups
|  ID  |  Team1  |  Team2  |
|  1    |     1       |      2      |

I want to query the matchups table and pull the teamnames from the teams table.

"SELECT matchups.team1, teams.teamname as team1, matchups.team2, teams.teamname as team2 FROM matchups join teams on matchups.team1 = teams.id "

The join has one team id = to the id in the team table, but what about the other team?
");

#2 sqlmc

sqlmc
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 23 November 2005 - 03:48 AM

With this table structure I would try something like this:

SELECT A.team1
,B.teamname as team1
,A.team2
,C.teamname as team2

FROM matchups A
JOIN teams B
ON A.team1 = B.id
JOIN teams C
ON A.team2 = C.id

/*A B and C are table aliases which I used just to cut down on typing*/





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users