Multiple joins for 3 tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
kevrelland
Forum Commoner
Posts: 73
Joined: Mon Jan 08, 2007 7:41 am

Multiple joins for 3 tables

Post by kevrelland »

Hi
i'm trying to get this query to work but i can't see how

Code: Select all

 
SELECT alleys.alley_id, alleys.alley_name, fixtures.alley_id, fixtures.fixture_id, fixtures.division_num, fixtures.fixture_home_team_id, fixtures.fixture_away_team_id, fixtures.fixture_date, fixtures.fixture_time, teams.team_id, teams.team_name
FROM fixtures LEFT JOIN alleys ON alleys.alley_id = fixtures.alley_id AND  teams ON teams.team_id = fixtures.fixture_home_team_id AND teams ON teams.team_id = fixtures.fixture_away_team_id
it has three tables, fixtures, alleys and teams
and i want to get the names to replace the id numbers referenced in the fixtures table
Can anyone help me
Cheers
Kevin
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Multiple joins for 3 tables

Post by jayshields »

Code: Select all

SELECT 
  *
FROM 
  fixtures 
LEFT JOIN alleys ON alleys.alley_id = fixtures.alley_id 
LEFT JOIN teams ON teams.team_id = fixtures.fixture_home_team_id 
LEFT JOIN teams ON teams.team_id = fixtures.fixture_away_team_id
?
kevrelland
Forum Commoner
Posts: 73
Joined: Mon Jan 08, 2007 7:41 am

Re: Multiple joins for 3 tables

Post by kevrelland »

i get
Not unique table/alias: 'teams' error
any ideas, but the rest works brilliant
cheers
Kev
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Multiple joins for 3 tables

Post by Eran »

Jay forgot to give unique aliases to the different joins (you can't have the same table name / alias appearing twice), thus:
[sql]SELECT  *FROM  fixturesLEFT JOIN alleys ON alleys.alley_id = fixtures.alley_idLEFT JOIN teams AS hteams ON hteams.team_id = fixtures.fixture_home_team_idLEFT JOIN teams AS ateams ON ateams.team_id = fixtures.fixture_away_team_id[/sql]

Also you should specify individual columns instead of a wildcard and not let MySQL decide which columns to take (columns from the same table will override each other unless you give them different aliases)
User avatar
jayshields
DevNet Resident
Posts: 1912
Joined: Mon Aug 22, 2005 12:11 pm
Location: Leeds/Manchester, England

Re: Multiple joins for 3 tables

Post by jayshields »

Yeah, that's it. I just selected all for simplicities sake, you can expand that however you want. Ofcourse you will have to prefix field names with the new aliases now rather than the actual table names.
kevrelland
Forum Commoner
Posts: 73
Joined: Mon Jan 08, 2007 7:41 am

Re: Multiple joins for 3 tables

Post by kevrelland »

Cheers guys
that works great
kevin
Post Reply