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:[EMAIL PROTECTED]] 
Sent: Thursday, July 12, 2001 3:01 AM
To: [EMAIL PROTECTED]; 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 <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: 
>[EMAIL PROTECTED]
>
>


-- 
PHP Database 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 Database 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]

Reply via email to