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@xxxxxxxxxxxxx>
To: "Han" <han@xxxxxxxxxxxxx>
Cc: <php-db@xxxxxxxxxxxxx>
Sent: Wednesday, January 12, 2005 4:06 PM
Subject: 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>
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
