SQL: problems with like and NOT LIKE
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 开发者_如何学Pythonreturned, 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,
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')
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*')
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 AND
s. It might be seeing one of the OR
s as true and thinking the whole line is true
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,*');
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.
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.
Don't use OR use AND this will work correctly
精彩评论