引用多个外键php mysql

Question

I'm very new to php/MySQL and I'm having a bit of trouble. Help would be much appreciated.

I have 2 tables laid out as such:

table team

team_id,team_name

table schedule

game_id,game_time,team1_id,team2_id,location

schedule.team1_id and schedule.team2_id are both foreign keys to team.team_id.

I'm trying to reference team_name using team1_id and team2_id but I can only ever seem to get the name for team1. This is the query I've used unsuccessfully.

SELECT * FROM team
AS t JOIN schedule AS s 
ON t.team_id = s.team1_id 
WHERE location='1';

My attempt to output the data:

while (mysql_fetch_assoc($result)) {
    echo $row['team_name'];
}

It's quite obvious to me why this isn't working, as the tables are joined on only one of the columns I need. Help! I'm completely lost as to how to solve this problem. I believe there is a simple solution, but I can't seem to find it!

Answer 1

It's not quite clear on what you would like the results of your query to look like, but I am assuming you would like to get information about both teams involved in each game. To do so, you'll have to join the team table twice:

SELECT t1.name, t2.name, s.* 
FROM schedule AS s 
INNER JOIN team AS t1 ON t1.team_id = s.team1_id 
INNER JOIN team AS t2 ON t2.team_id = s.team2_id 
WHERE s.location='1';