Re: Left Join is producing duplicate results - MySQL & relational tables

Mike - In general, you should always be reading the manual first. From the SELECT syntax at http://www.mysql.com/doc/S/E/SELECT.html GROUP BY must be indicated before ORDER BY. So reverse the order of those portions. - Ken At 10:35 PM 7/11/01 -0400, Mike Gifford wrote: >It looked good, but it gave me an error.. Sorry >... >WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID > >MySQL Error: 1064 (You have an error in your SQL syntax near 'GROUP BY WLPbib.bibID ' >at line 10) >Please contact the webmaster and report the exact error message. >Session halted. > >Ken wrote: > >>Mike - >>I'm not certain but it sounds like you might be looking for "GROUP BY". Do >>GROUP BY and then the columns that are identical in your results. >>- Ken >>At 09:46 PM 7/11/01 -0400, Mike Gifford wrote: >> >>>Hello, >>> >>>I posted this to the general list this morning & got a couple of good leads, but >>>they weren't able to actually fix the problem, so I'm posting here to the db list. >>> >>>I'm making some headway on joining three MySQL tables. >>> >>>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... >>> >>>Someone wrote back suggesting that SELECT DISTINCT could be used to to the job. >>> >>>Another person suggested that using UNIQUE(profileID) would make it look nicer. I >>>wasn't sure how to use UNIQUE with the last JOIN as it isn't directly linked to WLPbib.. >>> >>>Any suggestions would be useful. >>> >>>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 Database Mailing List (http://www.php.net/) >To unsubscribe, e-mail: php-db-unsubscribe@lists.php.net >For additional commands, e-mail: php-db-help@lists.php.net >To contact the list administrators, e-mail: php-list-admin@lists.php.net >