List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:December 12 2009 9:38pm
Subject:Re: Multiple joins from same table?
View as plain text  
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


Thread
Multiple joins from same table?Terry Van de Velde10 Dec
  • Re: Multiple joins from same table?Gary Smith10 Dec
  • Re: Multiple joins from same table?Shawn Green12 Dec
    • RE: Multiple joins from same table?Terry Van de Velde13 Dec