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]