Joins

[vertmonkee]

I have a table which contains called teams which contains team_id and team_name.

There is also another table called fixtures which contains match_id, home_team, second_team, third_team and date.

The three team fields in fixtures contain the teams id number.

When I wish to display the fixtures I want the team names to appear.

I have tried using a join but can only get one team name to display.

Here is my code so far.

$sql = "SELECT fx.*, tm.team_name " .
          "FROM fixtures fx " .
          "LEFT OUTER JOIN teams tm ON fx.home_team = tm.team_id " .
          "ORDER BY date";
$result = mysql_query($sql) or die(mysql_error());

echo "<table border=\"2\">";
echo "<tr><th>Date</th><th>Home Team</th><th>Second Team</th><th>Third Team</th></tr>";

while ($row = mysql_fetch_array($result)) {
	extract($row);
	print_r($row);
	echo "<tr><td>" . date("d-m-Y", strtotime($date)) . "</td><td>$team_name</td><td>$team_name</td><td>$team_name</td></tr>";
}

echo "</table>";

How can I get all three team names displaying.

Thanks.

[jheitz]

You need to join 3 times with the same table. Hope that helps