unusual behaiour with MySQL LIKE and LEFT join

I have a form which has a field for filtering a query, the code behine
the form uses the LIKE clause. When I enter "a%" in the field the form
returns all records starting with "a" (as expected.) But when I enter
"e%" no rows are displayed even though there is one record on my
database starting with "e". when. An SQL count with the same clauses
returns 1 record (as expected.) I isloated the discrepancy to two SQL
calls:

SELECT COUNT(*) FROM `testtbl` as a LEFT JOIN `testreftbl2` AS b ON
a.`testref2`=b.`id`,`testreftbl1` AS c WHERE a.`testtext` LIKE "e%"
AND a.`testdbl` LIKE "%" AND a.`testref1`=c.`id`;

no. of records=1

SELECT
a.`testid`,a.`testtext`,a.`testdbl`,a.`testopt`,a.`testradio `,b.`disp`,c.`disp`,a.`testchk`
FROM `testtbl` as a LEFT JOIN `testreftbl2` AS b ON
a.`testref2`=b.`id`,`testreftbl1` AS c WHERE a.`testtext` LIKE "e%"
AND a.`testdbl` LIKE "%" AND a.`testref1`=c.`id` ORDER BY a.testtext
ASC,a.testdbl ASC LIMIT 1,10;

no. of records=0

Re: unusual behaiour with MySQL LIKE and LEFT join

On Tue, 21 Aug 2007 16:30:28 +0200, D_a_n_i_e_l =

wrote:

> I have a form which has a field for filtering a query, the code behine=

> the form uses the LIKE clause. When I enter "a%" in the field the form=

> returns all records starting with "a" (as expected.) But when I enter=

> "e%" no rows are displayed even though there is one record on my
> database starting with "e". when. An SQL count with the same clauses
> returns 1 record (as expected.) I isloated the discrepancy to two SQL=

> calls:
>
> SELECT COUNT(*) FROM `testtbl` as a LEFT JOIN `testreftbl2` AS b ON
> a.`testref2`=3Db.`id`,`testreftbl1` AS c WHERE a.`testtext` LIKE "e%"
> AND a.`testdbl` LIKE "%" AND a.`testref1`=3Dc.`id`;
>
> no. of records=3D1
>
> SELECT
> a.`testid`,a.`testtext`,a.`testdbl`,a.`testopt`,a.`testradio `,b.`disp`=
,c.`disp`,a.`testchk`
> FROM `testtbl` as a LEFT JOIN `testreftbl2` AS b ON
> a.`testref2`=3Db.`id`,`testreftbl1` AS c WHERE a.`testtext` LIKE "e%"
> AND a.`testdbl` LIKE "%" AND a.`testref1`=3Dc.`id` ORDER BY a.testtext=

> ASC,a.testdbl ASC LIMIT 1,10;
>
> no. of records=3D0

This is an SQL question. For the sake clarity and a decent answer, you'r=
e =

better of asking this in an SQL group (alt.php.sql comes to mind, if you=
=

use MySQL (I think so, backticks..) comp.databases.mysql is also a good =
=

one).

-- =

Rik Wasmus

Re: unusual behaiour with MySQL LIKE and LEFT join

OK, thanks.