Joining tables with two values

[cliftonbazaar]

$teamID=101;  #This is the ID of our own team

 

If I have table MATCHES

matchID, tournament, homeTeamID, awayTeamID, e.t.c

1, 1, 101, 98

2, 2, 98, 99

3, 3, 101, 100

4, 4, 99, 100

5, 4, 100, 101

 

and table TOURNAMENTS with the data

tournamentID, winner, loser

1, 101, 98

2, 99, 98

3, 101, 100

4, 99, 100

 

and table TEAMS with data

teamID, name

98,  Darren

99, Danika

100, Emma

101, James

 

How would I print out the following - "tournamentID, Winners name, losers name" - if the team was involved with the tournament?

Print out with this data would be-

1, James, Darren

3, James, Emma

4, Danika, Emma

 

I know that a JOIN is to be used but I can only find if for one link where as I need to link it to both the winner and loser.

 

At the moment my code is, but it doesn't work anymore and doesn't look for the losers name

echo "<TR><TH COLSPAN=8><font size='6'>PREVIOUS TOURNAMENTS</TH>";
	$tournaments=mysql_query("
		SELECT tournament FROM matches
		WHERE homeTeamID='$teamID' or awayTeamID='$teamID'
		GROUP BY tournament
		ORDER BY tournament desc
		");
	while($tournament=mysql_fetch_array($tournaments)) {
		$tournament=$tournament['tournament'];
		$tournament_details=mysql_fetch_array(mysql_query("
			SELECT *.tournament, teamID.teams, name.teams
			FROM tournaments
			INNER JOIN teams
			ON winner.tournament = teamID.teams
			"));
		echo "<TD align='center'>".$tournament_details['tournamentID']."</TD>";
		echo "<TD colspan=2>".$tournament_details['winner']."</TD>";
		echo "<TD colspan=2>".$tournament_details['loser']."</TD>";
	}

[Barand]

Join twice to the teams table but using different table aliases

SELECT t.tournamentID, w.name as winner, l.name as loser
FROM tournaments t
INNER JOIN teams w ON t.winner = w.teamID
INNER JOIN teams l ON t.loser = l.teamID

Edited April 20, 2013 by Barand