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.
Terry Van de Velde
Phone: (519) 685-0295
Cell: (519) 619-0987
From: Shawn.Green@stripped [mailto:Shawn.Green@stripped]
Sent: December 12, 2009 4:39 PM
To: Terry Van de Velde
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
> my query return with the value in the visitor and home columns replaced
> 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
> 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)
> FROM schedule, sojhlteams
> 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
, 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=1
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