Re: Duplication Results When LEFT JOIN is Used Between Multiple Tables

Hi Max, Maxim Maletsky wrote:

not to give you the solution, but there's something you can simplify in your query: USING(field)

I like the idea of using USING... however I've run into some problems.

mysql_query("SELECT WLPbib.bibID,

          WLPbib.title,
          WLPbib.publisher,
          WLPbib.publicationDate,
          WLPaddress.city,
          WLPaddress.state,
          WLPprofile.firstName,
          WLPprofile.lastName,
          WLPprofile.organization,
          WLPcountry.languageName

FROM WLPbib

          LEFT JOIN WLPprofile USING(profileID)
          LEFT JOIN WLPaddress USING(publisherID)
          LEFT JOIN WLPcountry USING(countryID)");

simplier, right?

Simpler yes, however it produces this error: MySQL Error: 1054 (Unknown column 'WLPprofile.publisherID' in 'on clause') Because although WLPbib is linked to WLPprofile & WLPaddress it is not linked to WLPcountry. WLPaddress needs to be linked to WLPcountry. How does one specify that?

as of your problem, let me think, I had the same problem couple month ago. I'll look int omy codes.

I hear ya.. Somewhere, within which project lies the answer (likely).. Now which one? :) Thanks for looking into it. Mike

-----Original Message----- From: Mike Gifford [mailto:mike@openconcept.ca] Sent: Thursday, July 12, 2001 1:04 AM To: php-general@lists.php.net Subject: [PHP] Duplication Results When LEFT JOIN is Used Between Multiple Tables Hello, I'm making some headway on joining three MySQL tables. (Thanks to responses from this list early this morning) However, when I run this query: mysql_query("SELECT WLPbib.bibID,

          WLPbib.title,
          WLPbib.publisher,
          WLPbib.publicationDate,
          WLPaddress.city,
          WLPaddress.state,
          WLPprofile.firstName,
          WLPprofile.lastName,
          WLPprofile.organization,
          WLPcountry.languageName

FROM WLPbib

          LEFT JOIN WLPprofile ON WLPprofile.profileID = WLPbib.profileID
          LEFT JOIN WLPaddress ON WLPaddress.publisherID =

WLPbib.publisherID

          LEFT JOIN WLPcountry ON WLPcountry.countryID =

WLPaddress.countryID"); I now get results in triplicate. ie. I'm getting three copies of the same title, firstName, organization, etc.... I somehow suspected that this should be the result with LEFT JOIN, but I'm not sure how to return a query without duplication. This is far better than what I had this morning (which was no response from the server). Thanks. I'm new to joining tables... Mike -- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything your organization needs for an effective web site. Abolish Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that curiosity survives formal education. - A Einstein -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: php-general-unsubscribe@lists.php.net For additional commands, e-mail: php-general-help@lists.php.net To contact the list administrators, e-mail: php-list-admin@lists.php.net

-- Mike Gifford, OpenConcept Consulting, http://openconcept.ca Offering everything your organization needs for an effective web site. Abolish Nuclear Weapons Now!: http://pgs.ca/petition/ It is a miracle that curiosity survives formal education. - A Einstein