RE: [PHP-DB] Left Join is producing duplicate results - MySQL & relational tables

What about... SELECT WLPbib.bibID, WLPbib.title, WLPbib.publisher, WLPbib.publicationDate, WLPaddress.city, WLPaddress.state, WLPprofile.firstName, WLPprofile.lastName, WLPprofile.organization, WLPcountry.languageName FROM WLPprofile, WLPaddress, WLPcountry WHERE WLPprofile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I had a similar problems with a database I was working with... It had a main table with 29,000 listings, and I decided to normalize it to improve query times (split the records into diff. tables, one for each attribute of the record, associate the records back together by a common ID than spanned all the tables)... I ended up with an SQL query that spanned like 10 tables-- but it was -way- faster than one big table. There's an excellent article on Normalization on PHPbuilder -- http://www.phpbuilder.com/columns/barry20000731.php3 If your WLP tables are very large, you may want to try using mySQL's EXPLAIN SELECT [rest of select query]... function to figure out the best (read: efficient) ways of performing this query... Indexes are definitely a must if you are dealing with a lot of rows... If not, you should be just fine with the above query-- which I -think- is equivilent... I don't have a ton of experience with SQL, so perhaps someone can better elaborate. Best of luck! -----Original Message----- From: Dobromir Velev [mailto:diadomraz@rambler.bg] Sent: Thursday, July 12, 2001 3:01 AM To: php-db@lists.php.net; Mike Gifford Subject: Re: [PHP-DB] 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 > > -- 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