Subject |
Re: SQL Query like/not like with and/or |
From |
trevor <bob@bob.com> |
Date |
Mon, 02 Sep 2024 17:36:15 -0400 |
Newsgroups |
dbase.getting-started |
Mervyn Bick wrote:
> On 2024/09/02 14:13, trevor wrote:
> > Windows 10- dBase PLUS 2.21
> >
> > Using where to create a sql query I have found a difference between
> > using 'like' and 'not like' in conjunction with or/and
> > Using a simplified table as example
> >
> > abname.dbf
> > (single field),bname
> > records rows (char)
> > TP, JM, TPJM,SP, MP
> >
> > so using where like with or
> >
> > select * from abname where bname like 'T%' or bname like 'J%'
> > returns rows TP,JM, TPJM
> > - result correct as expected
> >
> > using where not like with or
> >
> > select * from abname where bname not like 'T%' or bname not like
> > 'J%' returns All records rows
> > not correct expected only SP,MP
>
> Wrong expectations. :-) If bname doesn't start with 'T' it means it
> starts with any other character including 'J', 'S' and 'M'. If bname
> doesn't start with 'J' it means it starts with any other character
> including 'T', 'S' and 'M'. With the two test OR'ed, if either test
> evaluates as true then the record is included so you wind up with all
> the records
>
> >
> > However using "and" -not- "or"
> >
> > select * from abname where bname not like 'T%' and bname not
> > like J%' returns SP, MP
> > as desired but unexpected as only single field row.
>
> When you AND two tests a record will only be included in the rowset
> where both tests evaluate as true. Your test table has two records
> where bname starts with 'T', and one record each for bname starting
> with 'J', 'S', or 'M'. The only time both tests evaluate as true is
> where bname starts with 'S' or 'M'. If your test table had more
> records with bname containing values such as 'AB', 'CD' and 'XY'
> these would also be included in the rowset together with 'SP' and
> 'MP'.
>
> >
> > using
> > select * from abname where bname like 'T%' and bname like 'J%'
> > returns no records as expected.
>
> Correct. The first character in bname can be any character but it
> can't be two characters at the same time. A record is included in
> the rowset only if both tests evaluate as true.
>
> >
> > It would appear that 'or' with "like" works/queries on each rows as
> > expected but 'and' with "not like" appears to works/queries the
> > whole table as one.
> >
> > Can anyone offer explanation where using 'and' in 'not like' gives
> > a correct result where I would have expected the use of 'or' to be
> > correct.
>
> The LIKE predicate only works with character fields. It depends, of
> course, on what you're doing but it is unusual to use NOT LIKE in a
> SELECT statement's WHERE clause as it will normally return more
> records in the rowset than it rejects.
>
> Mervyn.
Mervyn,
Thanks for your reply (once again).Thanks for the explanation.
I obviously need to brush up on my logic and work through the steps
more thoroughly.
Trevor
|
|