[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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
RE: [PHP] Duplication Results When LEFT JOIN is Used Between Multiple Tables
not to give you the solution, but there's something you can simplify in your query: USING(field) 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? as of your problem, let me think, I had the same problem couple month ago. I'll look int omy codes. -Maxim Maletsky -Original Message- From: Mike Gifford [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 1:04 AM To: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- PHP General Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
Re: [PHP] 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:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 1:04 AM To: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] -- 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
