List:General Discussion« Previous MessageNext Message »
From:Terry Van de Velde Date:December 10 2009 3:31pm
Subject:Multiple joins from same table?
View as plain text  
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.

 

Best Regards,

Terry

 


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