JOINing multiple fields from table1 to the same field in table2

[amy_rockville]

I am new to php/MySQL, so I apologize in advance for any stupidity on my part. I have a MySQL database for events, and the main table ("Brochure") has multiple Trip Leader fields, e.g., "Leader," "Assistant Leader," "Leader in Training," etc. All of these leader fields get their data from the same "Leaders" table.

So, the data in "Brochure" looks like this:

Trip_Name | Leader | Leader_Asst
-------------------------------------------
Fun Hike | 45 | 29

... and the data in "Leaders" looks like this:

ID | Name
----------------------
45 | Sally Smith
29 | Jane Doe

I am working on a PHP report that will show us all the trips and the leaders. I so far have figured out how to translate one field into the real name, but I am stumped on how to translate more than one field.

Here's what I've got so far:

...
$query = "SELECT Brochure.*, Leaders.Name ".
	"FROM Brochure LEFT JOIN Leaders ON Brochure.Leader=Leaders.ID ".
	"WHERE Brochure='2010 spring' AND Activity='biking' ".
	"ORDER BY Date_Full";
	
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<table width=590 cellpadding=0 cellspacing=0 border=0>";
echo "<tr><td colspan=2 class=TableTitlePrint>Leaders</td></tr>";
echo "<tr><td colspan=2>&nbsp;</td></tr>";

$i=0;
while ($i < $num) {
$Trip_Name=mysql_result($result,$i,"Trip_Name");
$Leader=mysql_result($result,$i,"Leader");
$Leader_Asst=mysql_result($result,$i,"Leader_Asst");
$Name=mysql_result($result,$i,"Name"); 

?>

<tr>
  <td class="TableHeaderPrint" valign="top"><div class="TableHeaderPrint" style="text-align: right;">Trip Name</div></td>
  <td class="TableBodyPrint" xwidth="300" valign="top"><? echo "$Trip_Name"; ?></td>
</tr>
<tr>
  <td class="TableHeaderPrint" valign="top"><div class="TableHeaderPrint" style="text-align: right;">Leader</div></td>
  <td class="TableBodyPrint" xwidth="300" valign="top"><? echo "$Name"; ?></td>
</tr>
<tr>
  <td class="TableHeaderPrint" valign="top"><div class="TableHeaderPrint" style="text-align: right;">Assistant Leader</div></td>
  <td class="TableBodyPrint" xwidth="300" valign="top"><? echo "$Leader_Asst"; ?></td>
</tr>

<?
++$i;
} 
echo "</table>";

?>

This successfully translates the Leader field into its "friendly" name. I can't figure out how to translate the Leader_Asst field into the"friendly" name.

Thanks much for any help!

[urtrivedi]

there is no limit on joining same table again.

SELECT Brochure.*, Leaders.Name, la.name as asst_name FROM Brochure LEFT JOIN Leaders ON Brochure.Leader=Leaders.ID 
left outer join leaders as LA on Brochure.Leader_asst=La.ID 
WHERE Brochure='2010 spring' AND Activity='biking' ORDER BY Date_Full

[amy_rockville]

Thank you! That worked. For the record, my final $query statement looks like this:

$query = "SELECT Brochure.*, Leaders.Name AS Name, LA.Name AS Asst_Name, LA2.Name AS Asst_Name2, LIT.Name AS LIT, LIT2.Name AS LIT2 ".
	"FROM Brochure LEFT JOIN Leaders ON Brochure.Leader=Leaders.ID LEFT OUTER JOIN Leaders AS LA ON Brochure.Leader_Asst=LA.ID LEFT OUTER JOIN Leaders AS LA2 ON Brochure.Leader_Asst2=LA2.ID LEFT OUTER JOIN Leaders AS LIT ON Brochure.Leader_LIT=LIT.ID LEFT OUTER JOIN Leaders AS LIT2 ON Brochure.Leader_LIT2=LIT2.ID  ".
	"WHERE Brochure='2010 spring' AND Activity='biking' ".
	"ORDER BY Date_Full";