>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.
Sir, in what way does it fail to 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";
What's the $dir for? Does this statement fail in both PHP and the
MySQL interface, or only in PHP?
You can avoid dealing with aliases entirely if you normalize the
department table.
Bob Hall
>$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
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
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
