CODEWITHSUNDEEP
mysql
walid youssef
walid youssef

Reputation: 93

Can not exclude values with NOT IN

I have a query that calculates the number of records excluding certain values, I tried to use <>, NOT LIKE and NOT IN but no luck, these values still shown.

SELECT * 
FROM `stmaintble` 
WHERE `stantithromb` <> 'Non prescribed' 
AND `stantithromb` <> 'Aspirin  75-150 mg'

Also tried:

SELECT * 
FROM `stmaintble` 
WHERE `stantithromb` NOT IN('Non prescribed', 'Aspirin  75-150 mg')

This doesn't work either:

SELECT * 
FROM `stmaintble` 
WHERE `stantithromb` NOT IN('Non prescribed, Aspirin  75-150 mg')

Thank for help.

Upvotes: 0

Views: 44

Answers (2)

Terry Carmen
Terry Carmen

Reputation: 3886

Your exclusions will only work if there's an exact match.

SELECT * 
FROM `stmaintble` 
WHERE `stantithromb` <> 'Non prescribed' 
AND `stantithromb` <> 'Aspirin  75-150 mg'

would only filter out the records that match exactly "Non prescribed" or "Aspirin 75-150 mg", but would not filter out records that contained "Aspirin 75-150 mg" (one space after Aspirin, not two), or "Non-prescribed" or "Non prescribed."

Also, it's possible the database has case-sensitive collation, in which case, "Aspirin" would not match "ASPIRIN".

You can use LIKE and a wildcard if you need to, as well as uppercase your search string and the data field if case sensitivity is required, however this will slow your search considerably.

Terry

Upvotes: 0

Jim
Jim

Reputation: 22656

If stantithromb has other text in that field (i.e. values look like 'Non prescribed some drug'). Then you'll need to use a wildcard LIKE search:

SELECT * 
FROM stmaintble 
WHERE stantithromb NOT LIKE '%Non prescribed%' 
  AND stantithromb NOT LIKE '%Aspirin  75-150 mg%'

Upvotes: 2

Related Questions