Re: LEFT joins

Hmm, still no luck. Thanks for the help. I think I'll have to break the select up into 2 selects and throw the results of the first into arrays. Didn't want to do it like that, but it's gonna be quicker now. Han. ----- Original Message ----- From: "Jochem Maas" <jochem@iamjochem.com> To: "Han" <han@fonedream.com> Cc: <php-db@lists.php.net> Sent: Wednesday, January 12, 2005 4:06 PM Subject: Re: [PHP-DB] 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>

-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php