Shawn, Thanks for the info, it does help indeed.
I had also replied back to Gary to thank him as well, but I don't think that it made it to the list... so to Gary, thanks as well. Regards, Terry Terry Van de Velde Email: bya...@rogers.com Phone: (519) 685-0295 Cell: (519) 619-0987 -----Original Message----- From: shawn.gr...@sun.com [mailto:shawn.gr...@sun.com] Sent: December 12, 2009 4:39 PM To: Terry Van de Velde Cc: mysql@lists.mysql.com Subject: Re: Multiple joins from same table? Terry Van de Velde wrote: > Good Day, > > I am attempting to do something new (to me) with MySQL. I am looking to have > my query return with the value in the visitor and home columns replaced with > the corresponding team name from the teams table. schedule.visitor and > schedule.home are essentially foreign keys to teams.team_no (though I have > not defined them as such yet). What I have been trying is the select > statement below which is fine when joining using one team (say the home > team), but as soon as I attempt to add in the visitor team, things fall > apart. > > I am basically looking to have the following outcome: > Oct. 30 - Titans vs. Hawks (7:30 PM) > Nov. 1 - Red Jackets vs. Boomerangs (8:30 PM) > > I would like this handled by MySQL instead of PHP if possible. > > Schedule table > 'id' int, > 'date_time' datetime, > 'visitor' tinyint > 'home' tinyint > > > teams table > 'id' int > 'team_no' smallint, > 'team_name' varchar (20) > > SELECT > schedule.date_time, > teams.team_name > FROM schedule, sojhlteams > WHERE > schedule.visitor = teams.team_no > > Any help is appreciated. > The trick to using the same table two or more times in the same query is through something called "aliases" when you alias a column or table you give it a different name and will make it easier to manage. Something like this is what you are after SELECT s.date_time , th.team_name home_team , ta.team_name away_team FROM schedule s INNER JOIN teams th ON th.team_no = s.home INNER JOIN teams ta ON ta.team_no = s.visitor Here you can see that I aliased the `teams` table twice. Once to handle the "home team" information (th) and once for the away team info (ta). I also aliased the team_name columns to make them less confusing labeling one as "home_team" and other as "away_team". I think that once you get a grip on how to use aliases, all of this multiple-table stuff will start to become much easier. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=bya...@rogers.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.716 / Virus Database: 270.14.105/2561 - Release Date: 12/12/09 14:39:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
