SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast Mangofreak's Avatar
    Join Date
    Aug 2004
    Location
    Toronto
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Please help with this query

    I have one table called schedules and one teams. On the schedules table I have intTeam1 and intTeam2, however, both fields come from the same table and field Teams.teamID.

    If I only use WHERE schedules.intTeam1 = Teams.teamID works only for one colum.

    How can make this work?

    Code:
    SELECT schedules.scheduleID, schedules.intTeam1, schedules.intTeam2, Teams.teamID, Teams.teamName
    FROM schedules, Teams
    WHERE schedules.intTeam1 = Teams.teamID AND schedules.intTeam2 = Teams.teamID

    Any help is much appreciated,

    Javier

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    695
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select schedules.scheduleID, 
           schedules.intTeam1, 
           schedules.intTeam2, 
           Team1.teamName,
           Team2.teamname
      from schedules, 
           Teams as team1,
           Teams as team2
     where schedules.intTeam1 = Team1.teamID
       and schedules.intTeam2 = Team2.teamID

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    SELECT schedules.scheduleID
         , schedules.intTeam1
         , schedules.intTeam2 
         , Team1.teamName
         , Team2.teamname
      FROM schedules
    INNER
      JOIN Teams AS team1
        ON Team1.teamID = schedules.intTeam1
    INNER
      JOIN Teams AS team2
        ON Team2.teamID = schedules.intTeam2
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast Mangofreak's Avatar
    Join Date
    Aug 2004
    Location
    Toronto
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Guys, thanks for the replies. Unfortunately still no luck. I get the following error.

    MM_ERROR: MySQL Error#: 1054 Unknown column 'Team1.teamName' in 'field list'

    Perhaps my initial query is not enough to show my intentions. Please let me know what would it make clearer? I have to take care of my kids but I'll be playing with INNER joins


    Javier

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    case sensitive?
    Code:
    SELECT schedules.scheduleID
         , schedules.intTeam1
         , schedules.intTeam2 
         , Team1.teamName
         , Team2.teamname
      FROM schedules
    INNER
      JOIN Teams AS Team1
        ON Team1.teamID = schedules.intTeam1
    INNER
      JOIN Teams AS Team2
        ON Team2.teamID = schedules.intTeam2
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Enthusiast Mangofreak's Avatar
    Join Date
    Aug 2004
    Location
    Toronto
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937,

    That works very nicely. Thank you very much.

    Javier


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
  •