Join problem

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
Krokkodriljo
Forum Newbie
Posts: 1
Joined: Fri Mar 26, 2004 2:13 pm

Join problem

Post by Krokkodriljo »

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
Post Reply