SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with SELECT (and joins) in MySQL

    Hi:

    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
    etc.

    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,334
    Mentioned
    63 Post(s)
    Tagged
    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
    note i wrote this with "rtid" whereas you said you have "VTID" -- but i think you can figure that out, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict Phil-man's Avatar
    Join Date
    Nov 2000
    Posts
    291
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •