Results 1 to 3 of 3
Jul 20, 2002, 09:05 #1
Problem with SELECT (and joins) in MySQL
I have a table ("Teams") that stores team names in a field called "Team". Then I have another table ("Games"), which includes, among other fields, "HTID", which is the home team ID, and "VTID", which is the visiting team ID. Each of those fields (HTID and VTID) corresponds to the ID field in the Teams table.
I want to be able to pull the data from the "Games" table and print something like this:
Marauders at Maulers
Destroyers at Devastators
But I can't figure out how to get a join to work in this case, since I need to pull the same field ("Team") twice from the Teams table, once for HTID and once for WTID.
That is, I can't do "SELECT Team AS HomeTeam, Team AS RoadTeam...", or if it's possible I can't figure out how to get it to work. I can normally do joins no problem, but this is a bit different.
Any ideas out there? Thanks!
Jul 20, 2002, 10:52 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 59 Post(s)
- 3 Thread(s)
you were so close...Code:
select ht.Team AS HomeTeam , rt.Team AS RoadTeam from games inner join teams as ht on htid = ht.id inner join teams as rt on rtid = rt.id
Jul 20, 2002, 18:16 #3
Very cool. THANK YOU. Works perfectly. After I got your response, I found the material covered in Kevin Yank's book ("Build Your Own..."). I remember reading that part originally and thinking, "When the heck am I ever going to use THAT?" and I just kind of glossed over it.