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