Help accessing data across multiple tables

[tuttle425]

I am trying to reference values across two tables.  I think I have the correct query but need help referencing the values:

 

I can get the first and last name's of the first coach just fine but I cant figure out how to get the first and last name's of the second coach.

 

Here are the two tables in question:

 

teams

team_idcoach1coach2

20091317

20081412

 

coaches

 

coach_idfirst_namelast_name

12bobsmith

13johndoe

14jimjohnson

17fredwhite

 

Those are the two tables but imagine about 1000 more entries and even more columns (but they dont have anything to do with this query so I didnt list them).

 

Here is my php code:

 

$sql = "SELECT * from teams a, coaches b WHERE b.coach_id=a.coach1";
		$result = mysql_query($sql, $conn) or die($mysql_error());
		while ($team = mysql_fetch_array($result)) {
			$year = $team['team_id'];
			$coach_first = $team['first_name'];
			$coach_last = $team['last_name'];
			echo $year." - ".$coach_first." ".$coach_last."<br />";

 

Thanks a lot.

[Psycho]

SELECT team_id,
       c1.first_name as coach1_fname, c1.last_name as coach1_lname,
       c2.first_name as coach2_fname, c2.last_name as coach2_lname,

FROM teams t

JOIN coaches c1 ON t.coach1 = c1.coach_id
JOIN coaches c2 ON t.coach2 = c2.coach_id