Re: LEFT joins

Han wrote:

Oh yes, sorry, not used to the "reply-all".

most of us get hammered by that one once in a while! read on the the interesting bit...

I've pasted it directly into the MYSQL console as just sql and this is where I discovered my problem. My code is : -- SELECT b.fldName, b.fldEmail, b.fldCountryCode, b.fldMobile, a.fldTime as Time, c.fldUsername FROM tblSubscribersChoices a INNER JOIN tblUser c on a.fldChoice=c.fldClientID LEFT JOIN tblSubscribers b on a.fldClientID = b.fldID ORDER BY c.fldUsername ASC Now this works fine. But, I'm trying to tally the country code with it's actual name (which is held in tc_countries). So I thought if I add another LEFT JOIN at the end of the query : -- SELECT b.fldName, b.fldEmail, b.fldCountryCode, d.fldCode as FCode, b.fldMobile, a.fldTime as Time, c.fldUsername FROM tblSubscribersChoices a INNER JOIN tblUser c on a.fldChoice=c.fldClientID LEFT JOIN tblSubscribers b on a.fldClientID = b.fldID LEFT JOIN tc_countries d on

lets look just at this last little bit of the statement:

b.fldCountryCode = d.fldCode ORDER BY c.fldUsername ASC

try changing this to: d.fldCode = b.fldCountryCode ORDER BY c.fldUsername ASC (I removed the backticks too, as you don't use them in the rest of your query, and that was irritating the aethetic monster within me ;-) Actually looking at your complete statement you _seem_ to have got all the JOINS backwards (I'm no SQL guru so I may be totally wrong here, besides which I don't know the DB-schema your using), does this work: SELECT b.fldName,

       b.fldEmail,
       b.fldCountryCode,
       d.fldCode as FCode,
       b.fldMobile,
       a.fldTime as Time,
       c.fldUsername

FROM tblSubscribersChoices a LEFT JOIN tblUser c ON c.fldClientID = a.fldChoice LEFT JOIN tblSubscribers b ON b.fldID = a.fldClientID LEFT JOIN tc_countries d ON d.fldCode = b.fldCountryCode ORDER BY c.fldUsername ASC and if that works but doesn't give you repeat records try adding the DISTINCT keyword after SELECT. <snip>