Keith, You're on the right track. But instead of 2 tables, pretend you have three. 1) Home Teams, 2) Schedule 3) Opponent Teams. In reality tables 1 and 3 are the same table, but you mus't get confused between using 'Teams' as Home Teams and Teams as Opponents. In the same way as you named columns using 'AS' you can 'name' tables using AS.
E.G. select HomeTeams.Team_Name as 'Team Name' from Teams as HomeTeams; <-- notice how you seem to have a new table called 'HomeTeams'. This naming of table allows you to use the same table 2 or more times without getting confused as to which table you are refering to. Now you can join 3 'tables' instead of only 2. select HomeTeams.Team_Name as 'Team Name',Schedules.Team_ID,Schedules.Opponent_ID, OpponentTeams.Team_Name as 'Opponents', Schedules.Game_Date from Schedules,Teams as HomeTeams, Teams as OpponentTeams where (HomeTeams.id="140" or OpponentTeams.id ="140") and (HomeTeams.id=Schedules.Team_ID and OppenentTeams.id=Schedules.Opponent_ID) order by Game_Date; The 3 tables are joined in the last line. The previous line limits the results to where the home or opponents are '140'; Excuse any typos. Regards, Andy. "Warren, Keith" wrote: > > 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
