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 ASCand 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
