Table Join query

[lukep11a]

Hi, I have a fantasy football website, and on a user account page I want to display fixtures that are coming up that include teams that the current user has chosen. My test_teams table stores all the team names and their teamid. The test_selections table is where each users team selections are stored, it has two columns, userid and teamid. The test_fixtures table has two columns, hometeam and awayteam, these two cloumns hold the teamid of the teams that are playing.

 

The code below correctly displays the fixtures that contain any of the current users team selections. However, it is only displaying the teamid of the teams that are playing as they have not been matched to the test_teams table to get the team name. Does anybody now how I can do this? I believe it can be done using a left join but so far I just keep getting errors when i try to write the code. Any help would be very much appreciated.

 

<table width="380" border="0">
	<?php
	$query = "SELECT test_fixtures.competition, test_fixtures.date, test_fixtures.hometeam, test_fixtures.awayteam FROM test_fixtures, test_selections WHERE test_selections.userid = '{$_SESSION['userid']}' AND (test_selections.teamid = test_fixtures.hometeam OR test_selections.teamid = test_fixtures.awayteam)";
        $result = mysql_query($query) or die(mysql_error());
        
        while($row = mysql_fetch_assoc($result))
	{
	?>
	<tr>
	<td width="85" class="fixtures_date"><?php echo $row['date']; ?></td>
	<td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td>
	<td width="135" class="fixtures_home_teams"><?php echo $row['hometeam']; ?></td>
	<td width="25" class="fixtures_center">v</td>
	<td width="135" class="fixtures_away_teams"><?php echo $row['awayteam']; ?></td>
	</tr>
	<?php
	}
	?>
	</table>

[ericburnard]

Have a good read through this

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

I have only just worked out how to get my JOINS to work, but its because of this that they do. Makes them alot easier to get your head round!

[lukep11a]

Thanks for that, it explains joins really well. I am still struggling with mine though, because I am trying to join the test_fixtures table which contains 2 columns that need to be referenced to the test_teams table?