SQL: problems with like and NOT LIKE

Question

i have this statement in access:

SELECT *
FROM accountsnew
WHERE [Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not like '*OXY_S,*' and [Panels] not like '*OXY_N,*' and [Panels] like '*OXYC_SNEG,*' or [Panels] like '*OXYC_PNEG,*';

for some reason it is not returning the proper dataset. when i look more deeply at the data returned, using excel, i see that what it returned in fact DID contain IT_OXYC, and some other parameters.

is there something wrong with my statement?

i am trying to get all records which do not have IT_OXYC, OXY_SN , OXY_S, etc... and DO have either OXYC_SNEG, or OXYC_PNEG,

Answer 1

Your logic is wrong. Translating your request bit-by-bit:

I am trying to get all records

SELECT * FROM accountsnew

which do not

WHERE NOT (...)

have IT_OXYC or OXY_SN or OXY_S, etc...

(x LIKE '*IT_OXYC*' OR x LIKE '*IOXY_SN*' OR x LIKE '*OXY_S*' OR ...)

and DO have either OXYC_SNEG, or OXYC_PNEG,

AND (x LIKE '*OXYC_SNEG*' OR x LIKE '*OXYC_PNEG*')

Putting it all together:

SELECT *
FROM accountsnew
WHERE NOT ([Panels] LIKE '*IT_OXYC*' OR
           [Panels] LIKE '*IOXY_SN*' OR
           [Panels] LIKE '*OXY_S*' OR ...)
AND ([Panels] LIKE '*OXYC_SNEG*' OR 
     [Panels] LIKE '*OXYC_PNEG*')

Answer 2

It's probably just precedence... define it with braces to set the precedence: WHERE (panels NOT LIKE '1' AND panels NOT LIKE '2') AND (panels LIKE '3' OR panels LIKE '4')

Answer 3

Not sure if it makes a difference but I would put in brackets near the end so:

WHERE [Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not like '*OXY_S,*' and [Panels] not like '*OXY_N,*' and ([Panels] like '*OXYC_SNEG,*' or [Panels] like '*OXYC_PNEG,*');

You have one OR in a line containing only ANDs. It might be seeing one of the ORs as true and thinking the whole line is true

Answer 4

please pardon my dear aunt sally: You may need some parentheses to get this to do what you want. I suspect you need to place them at the end like so:

SELECT *
FROM accountsnew
WHERE ([Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not 
like '*OXY_S,*' and [Panels] not like '*OXY_N,*') 
and ([Panels] like '*OXYC_SNEG,*' or [Panels] like '*OXYC_PNEG,*');

Answer 5

I remember with Access it works better to say

SELECT *
FROM accountsnew
WHERE NOT [Panels] like '*IT_OXYC,*' 
    and not [Panels] like '*OXY_SN,*' 
    and not [Panels] like '*OXY_S,*' 

etc.

Answer 6

some of your records which contain oxyc_sneg and oxcy_pneg, also contain it_oxcy and others....

So, when you try to get records, where [Panels] like '*OXYC_SNEG,*' or [Panels] like '*OXYC_PNEG,*, it also gives you some records which contains both OXYC_SNEG and it_oxcy or OXYC_PNEG and it_oxcy etc...

so what can you do here is something like,

SELECT *
FROM accountsnew
WHERE { [Panels] like '*OXYC_SNEG,*' and 
{[Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not like '*OXY_S,*' and [Panels] not like '*OXY_N,*}
}
or 
{[Panels] like '*OXYC_PNEG,*' and  
{[Panels] not like '*IT_OXYC,*' and [Panels] not like '*OXY_SN,*' and [Panels] not like '*OXY_S,*' and [Panels] not like '*OXY_N,*} }

this should work fine, but just write in proper syntax before trying it out.