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

Hi, Did you try to use something like this. mysql_query("SELECT DISTINCT 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"); The other thing that may help is to rearange the order of the tables in the FROM clause. Please check if some of the joins return more than one result - if you have more than one address for a publisher the query will return one row for every address. Hope this helps Dobromir Velev -----Original Message----- From: Mike Gifford <mike@openconcept.ca> To: php-db@lists.php.net <php-db@lists.php.net> Date: Thursday, July 12, 2001 4:31 AM Subject: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables >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 > >