Just build the SQL statement piece by piece instead of all at once.
Be sure that your tables are setup properly, allowing you to make joins
properly. Try not to rush, cause you might miss a couple of little things
along the way.
Kevin Johnson
- Original Message -
From: Mike Gifford [EMAIL PROTECTED]
To: Ken [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Wednesday, July 11, 2001 08:00 PM
Subject: Re: [PHP-DB] Left Join is producing duplicate results - MySQL
relational tables
Sorry Ken,
I'm trying to learn a few too many things at once. Switching it around
eliminated the error message but not the duplication.
I'm eliminating as much of the code as I can to see that it isn't coming
from PHP.
Thanks again for your help.
Mike
Ken wrote:
In general, you should always be reading the manual first. From the
SELECT syntax at
http://www.mysql.com/doc/S/E/SELECT.html
GROUP BY must be indicated before ORDER BY. So reverse the order of
those portions.
- Ken
At 10:35 PM 7/11/01 -0400, Mike Gifford wrote:
It looked good, but it gave me an error.. Sorry
...
WLPcountry.countryID ORDER BY WLPbib.title GROUP BY WLPbib.bibID
MySQL Error: 1064 (You have an error in your SQL syntax near 'GROUP BY
WLPbib.bibID ' at line 10)
Please contact the webmaster and report the exact error message.
Session halted.
Ken wrote:
Mike -
I'm not certain but it sounds like you might be looking for GROUP BY.
Do GROUP BY and then the columns that are identical in your results.
- Ken
At 09:46 PM 7/11/01 -0400, Mike Gifford wrote:
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]
--
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]
