Page 1 of 1

Multiple joins for 3 tables

Posted: Fri Sep 04, 2009 9:48 am
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

Re: Multiple joins for 3 tables

Posted: Fri Sep 04, 2009 9:58 am
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
?

Re: Multiple joins for 3 tables

Posted: Fri Sep 04, 2009 10:15 am
by kevrelland
i get
Not unique table/alias: 'teams' error
any ideas, but the rest works brilliant
cheers
Kev

Re: Multiple joins for 3 tables

Posted: Fri Sep 04, 2009 11:25 am
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)

Re: Multiple joins for 3 tables

Posted: Fri Sep 04, 2009 12:29 pm
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.

Re: Multiple joins for 3 tables

Posted: Mon Sep 07, 2009 3:32 am
by kevrelland
Cheers guys
that works great
kevin