Re: LEFT joins

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux