List:General Discussion« Previous MessageNext Message »
From:Terry Van de Velde Date:December 13 2009 2:36am
Subject:RE: Multiple joins from same table?
View as plain text  
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: byakko@stripped
Phone: (519) 685-0295
Cell:  (519) 619-0987


-----Original Message-----
From: Shawn.Green@stripped [mailto:Shawn.Green@stripped] 
Sent: December 12, 2009 4:39 PM
To: Terry Van de Velde
Cc: mysql@stripped
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=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
14:39:00

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