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
