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

Hi Matthew, Matthew Loff wrote:

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 think that this is working now.

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)...

This one will likely have 10,000 to start with, so it is good to build in normalization... Didn't even know what the term normalization referred to earlier today. However, that is what I was doing with the table.

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

This is a good article. Even addressed the question that I fired off to Dobromir about linking various profiles to the same bibliography. One person can write many articles & an article can have many authors, so I need to create a many-many table to link the articles to profiles. I think that this wil work: CREATE TABLE WLParticle2profile ( a2pID mediumint(9) NOT NULL auto_increment, bibID mediumint(9), profileID mediumint(9), PRIMARY KEY (a2pID) ) I'll then need to Re-jig the WHERE command to limit the number of returns... This would become 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, WLParticle2profile WHERE WLParticle2profile.profileID = WLPbib.profileID AND WLPaddress.publisherID = WLPbib.publisherID AND WLPcountry.countryID = WLPaddress.countryID; I'm not sure this will work to tie in the relational table and then we come back to the problem with duplicate entries again...

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 didn't know about this option: http://www.mysql.com/doc/E/X/EXPLAIN.html I find the MySQL.com site to be hard to read through.. php.net is much easier to understand in my experience.

I don't have a ton of experience with SQL, so perhaps someone can better elaborate.

I think that you did a good job.. However I think I'm still stuck with the same duplicate error now (well when I've expanded the code. Mike

-----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

-- 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