Join problem
Posted: Fri Mar 26, 2004 2:13 pm
Okay, here's my problem.
I want to put all matches of the upcoming soccer season in a database. I have all the teams in one table and all games in another table.
Teams table
-------------------
id
name
arena
played
won
lost
draw
goals_made
goals_against
points
Matches table
-------------------
id
time
home_team
away_team
round
spectators
goals_home_team
goals_away_team
So, when I want to display all games in the first round for example, my query looks like this;
SELECT * FROM matches, teams WHERE matches.home_team=teams.id AND matches.away_team=teams.id AND matches.round=1 ORDER BY time ASC
This doesn't work and I think it's because of the "double join" of teams.id
The problem is that I want to display a table with all games from the first round
hometeam vs awaytem
anotherteam vs yetanotherteam
...and so on
But since the team names are taken from the teams table (only team ID number is stored in games table), how do I get the two names in each game? PHP code looks something like
$result = mysql_query ("SELECT * FROM matches, teams WHERE matches.home_team=teams.id AND matches.away_team=teams.id AND matches.round=1 ORDER BY time ASC");
while ($row = mysql_fetch_array ($result)) {
echo $row[team_name] . ' vs ' . $row[team_name] . '<br>';
}
See the problem? I can only get one team name...
Can someone tell me how to solve this?
Thanks
I want to put all matches of the upcoming soccer season in a database. I have all the teams in one table and all games in another table.
Teams table
-------------------
id
name
arena
played
won
lost
draw
goals_made
goals_against
points
Matches table
-------------------
id
time
home_team
away_team
round
spectators
goals_home_team
goals_away_team
So, when I want to display all games in the first round for example, my query looks like this;
SELECT * FROM matches, teams WHERE matches.home_team=teams.id AND matches.away_team=teams.id AND matches.round=1 ORDER BY time ASC
This doesn't work and I think it's because of the "double join" of teams.id
The problem is that I want to display a table with all games from the first round
hometeam vs awaytem
anotherteam vs yetanotherteam
...and so on
But since the team names are taken from the teams table (only team ID number is stored in games table), how do I get the two names in each game? PHP code looks something like
$result = mysql_query ("SELECT * FROM matches, teams WHERE matches.home_team=teams.id AND matches.away_team=teams.id AND matches.round=1 ORDER BY time ASC");
while ($row = mysql_fetch_array ($result)) {
echo $row[team_name] . ' vs ' . $row[team_name] . '<br>';
}
See the problem? I can only get one team name...
Can someone tell me how to solve this?
Thanks