Re: Newbie SQL question
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]
Newbie SQL question
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]
newbie: SQL question
Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: newbie: SQL question
http://hotwired.lycos.com/webmonkey/backend/databases/ might be a good place, haven't read it, but I send people that want to learn HTML to webmonkey, and they liked it. -Original Message- From: Admin-Stress [mailto:meerkapot;yahoo.com] Sent: Monday, November 04, 2002 10:50 AM To: [EMAIL PROTECTED] Subject: newbie: SQL question Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot __ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: newbie: SQL question
Try this: http://www.devshed.com/Server_Side/MySQL - Hi, I am just a starter. Anyone can suggest me good web resources for learning SQL command that I can use (compatible) with mySQL ? I read from www.mysql.com documentation, but it's not complete ... Well, if you have collection for beginner, please :) Thanks, kapot - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
