Oh yes, sorry, not used to the "reply-all".
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 b.fldCountryCode = d.fldCode ORDER BY `c`.`fldUsername` ASC
But this just times out as it's matching too many matches.
I should give me the same results as before, but with the country name added as well next to the b.fldCountryCode
----- Original Message ----- From: "Jochem Maas" <jochem@xxxxxxxxxxxxx>
To: "Han" <han@xxxxxxxxxxxxx>; <php-db@xxxxxxxxxxxxx>
Sent: Wednesday, January 12, 2005 1:51 PM
Subject: Re: LEFT joins
BTW: Hans please use 'reply to all' so that the rest of the list can see your replies (+ it makes me feel less like you personal help-desk ;-).
...on to the reply proper:
Han wrote:Hi, thanx for replying.
It still times out when I use either of those alternatives.
can you tell what exactly the script is timing out on - i.e. are you sure its the call to the DB? you SQL _may_ not be the most efficient way of doing it (not that I know or anything) but I can tell you that I have a query on a production site which involves 16 JOINS and that works fine [I can here you laughing in the bach there ;-)] - actually it had 33 JOINS but there is a hard limit to the number of joins per statement in the version of MySQL the site uses so I had to split the query into 2!
also have you tried running the complete query/queries directly in a MySQL console? i.e. remove PHP from the equation to determine if the problem is actually the SQL. please tell us whether the statements work ok when you run them directly.
I've tried changing all the JOINs around and nothing works.
Post the table deinfitions to the list + a recap on what you are trying to get as a result.
I thought maybe there's a logic problem going on?
some kind of problem anyway ;-)
<snip>
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 b.fldCountryCode = d.fldCode ORDER BY `c`.`fldUsername` ASC
in the orderby clause you apply backticks to the table alias 'c', which you don't do anywhere else. its a long shot but try removing those backticks.
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
