multiple inner join

[jonzy]

how to join two different fields from same table

table schedule_scores
id
date
time
arena_id
home_team
visiting_team

table arenas
id
arena_name

table teams
id
name

trying to get teams.name to go into both schedule_scores.home_team and schedule_scores.visiting_team. I can get it to work with one, but not both. any ideas? Thanks!

SELECT
schedule_scores.date,
schedule_scores.time,
arenas.arena_name,
teams.name
FROM
schedule_scores
INNER JOIN arenas ON (schedule_scores.arena_id = arenas.id)
INNER JOIN teams ON (schedule_scores.home_team = teams.id)

[harmor]

try

SELECT
schedule_scores.date,
schedule_scores.time,
arenas.arena_name,
teams.name
FROM
schedule_scores AS schedule_scores
LEFT JOIN arenas AS arenas ON (schedule_scores.arena_id = arenas.id)
LEFT JOIN teams AS teams ON (schedule_scores.home_team = teams.id)

[jonzy]

Thanks, but where does the (schedule_scores.visiting_team = teams.id) part take place? That's the problem I have, I can do one or the other (home_team or visiting_team).

[jheitz]

Hi Jonzy,

You have to join twice to the same table:

SELECT
schedule_scores.date,
schedule_scores.time,
arenas.arena_name,
TeamA.name,
TeamB.name
FROM
schedule_scores
INNER JOIN arenas ON (schedule_scores.arena_id = arenas.id)
INNER JOIN teams TeamA ON (schedule_scores.home_team = TeamA.id)
INNER JOIN teams TeamB ON (schedule_scores.visiting_team = TeamB.id)

[jonzy]

But then I get 'Failed to execute query', table sports.TeamA does not exist.
I am now pounding my head against the wall. :glare:

[liquorvicar]

Can you echo out the whole query you are trying to use so we can have a look? And what version of MySQL are you using?

[jheitz]

Maybe in MySQL you need to do

JOIN teams as TeamA

instead of

JOIN teams TeamA

Basically, you need an alias for the table, as you have two instances of it

[Roger_Ramjet]

WHOOPS, not in mysql.