Ok, I've answered my own question but now have another.
How do I reference the sitename for the 3 sites?
sitename returns the last sitename for all 3 I tried S1.sitename etc. but it
doesn't work.
$query = "SELECT * FROM department LEFT JOIN sites S1 ON
deptsite1=S1.sitekey LEFT JOIN sites S2 ON deptsite2=S2.sitekey ORDER BY
$order $dir";
$result = mysql_db_query($dbName,$query);
while ($r=mysql_fetch_array($result)) {
echo "<tr bgcolor=$colorvalues>
<td><font size=\"-1\">$r[deptdesc]</td>
</tr>
<tr bgcolor=$colorvalues>
<td><font size=\"-1\">$r[sitename]</td>
<td><font size=\"-1\">$r[deptphone1]</td>
<td><font size=\"-1\">$r[deptfax1]</td>
<td><font size=\"-1\">$r[deptemail1]</td>
<td><font size=\"-1\">$r[deptmobile1]</td></tr>
<td><font size=\"-1\">$r[sitename]</td>
<td><font size=\"-1\">$r[deptphone2]</td>
<td><font size=\"-1\">$r[deptfax2]</td>
<td><font size=\"-1\">$r[deptemail2]</td>
<td><font size=\"-1\">$r[deptmobile2]</td></tr>
<td><font size=\"-1\">$r[sitename]</td>
<td><font size=\"-1\">$r[deptphone3]</td>
<td><font size=\"-1\">$r[deptfax3]</td>
<td><font size=\"-1\">$r[deptemail3]</td>
<td><font size=\"-1\">$r[deptmobile3]</td>";
-----Original Message-----
From: Ross Goonan [mailto:[EMAIL PROTECTED]]
Sent: Friday, 8 June 2001 11:47
To: [EMAIL PROTECTED]
Subject: JOIN to the same table multiple times
############################################################################
###
Creating a Telephone / Information Directory with MySQL / PHP3
People belong to a department & a site.
Need to be able to:
List all people
List all people within a Department
List all people within a site
List all people within a Department & Site
Have set up Department table with site1, site2 & site3 as the same
department
exists in multiple sites.
When listing a site, I need to JOIN the site table multiple times to get the
site name.
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey
- Works no worries
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site ON
Site2=Sitekey
- Error 1066: Not unique table/alias 'Site'
SELECT * FROM Department LEFT JOIN Site ON Site1=Sitekey LEFT JOIN Site AS
Sitetable ON Site2=Sitekey
- Error 1052: Column 'Sitekey' in on clause is ambiguos
############################################################################
###
rpm -qa | grep SQL
MySQL-3.23.33-1
MySQL-client-3.23.33-1
MySQL-devel-3.23.33-1
rpm -qa | grep sql
php-mysql-3.0.16-2bc
perl-Msql-Mysql-modules-1.2210-2
############################################################################
###
People Table
----------------
Surname
Firstname
Department
site
Department Table
----------------
Name
Site1
site2
site3
Site Table
----------------
Sitekey
Sitename
############################################################################
###
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <mysql-unsubscribe-##L=##[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
