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

It looked good, but it gave me an error.. Sorry Database error: Invalid SQL: 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 WLPbib.profileID = WLPprofile.profileID LEFT JOIN WLPaddress ON WLPbib.publisherID = WLPaddress.publisherID LEFT JOIN WLPcountry ON WLPaddress.countryID = 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