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=arch...@jab.org
RE: Multiple joins from same table?
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
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. select * from table as t1,table as t2,table as t3 where t1.column1='blah' and t2.column2='blah' and t3.column3='blah' does the same thing as select * from table where column1='blah' and column2='blah' and column3='blah' Does that do what you're after? Gary -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
