Please help with this query

[Mangofreak]

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?

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

[swampBoogie]

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

[r937]

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

[Mangofreak]

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

[r937]

case sensitive?

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

[Mangofreak]

r937,

That works very nicely. Thank you very much.

Javier