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

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