I'm coming from a Filemaker Pro background and have very little SQL experience. I'm trying to write an SQL statement to extract data from two tables. One table has the Team IDs, Team Names for all the high school football teams in the state. The other table has the schedules for all the games. The schedules table has Game_ID, Team_ID, Opponent_ID and Game_Date fields.
I've got the SQL statement to return the data that I'm looking for, except, I only get team IDs. I want team names. This is the MySQL statement: mysql> select Teams.Team_Name as 'Team Name',Schedules.Team_ID,Schedules.Opponent_ID,Schedules.Game_Date from Schedules,Teams where Teams.id="140" and (Teams.id=Schedules.Team_ID or Teams.id=Schedules.Opponent_ID) order by Game_Date; and here are the results. +-----------------+---------+-------------+------------+ | Team Name | Team_ID | Opponent_ID | Game_Date | +-----------------+---------+-------------+------------+ | Lawrence County | 140 | 88 | 2003-08-28 | | Lawrence County | 163 | 140 | 2003-09-05 | | Lawrence County | 140 | 237 | 2003-09-12 | | Lawrence County | 140 | 161 | 2003-09-19 | | Lawrence County | 263 | 140 | 2003-09-26 | | Lawrence County | 129 | 140 | 2003-10-03 | | Lawrence County | 153 | 140 | 2003-10-10 | | Lawrence County | 140 | 284 | 2003-10-17 | | Lawrence County | 323 | 140 | 2003-10-24 | | Lawrence County | 140 | 26 | 2003-10-31 | +-----------------+---------+-------------+------------+ 10 rows in set (0.01 sec) This is exactly what I want, but, I want to be able to query the Teams table to give me the Team_Name for both the Team_ID (which is the Home team) and the Team_Name for the Opponent_ID (the visiting team). I'm assuming I'd have to have a nested search, but I really don't even know enough about SQL to ask an intellegent question here. Can someone point me in the right direction? Thanks, Keith Warren Systems Editor The Clarion-Ledger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
