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
> 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