CODEWITHSUNDEEP
mysqlsql
Mike Craford
Mike Craford

Reputation: 7

NOT LIKE clause is not working

Here is my query:

SELECT * 
FROM client_info
WHERE (cid LIKE 'G%' OR cid LIKE 'H%' OR cid LIKE 'J%')
AND (client_name NOT LIKE 'P4%' OR client_name NOT LIKE 'P5%')

The results still contain client names starting with P4 and P5. What is wrong with NOT LIKE clause?

Upvotes: 0

Views: 90

Answers (4)

Carl Manaster
Carl Manaster

Reputation: 40336

Others have given you the correct answer - you need to use AND instead of OR. But it's worth understanding why. Take a client named P5_JONES, for instance - why is this patient showing up in the list? Because its name is NOT LIKE 'P4%'. Yes, it is like P5%, but with an OR in there, only one of those expressions needs to be true to satisfy the condition.

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

Try this:

SELECT * 
FROM client_info
WHERE (cid LIKE 'G%' OR cid LIKE 'H%' OR cid LIKE 'J%')
AND (client_name NOT LIKE 'P4%' AND client_name NOT LIKE 'P5%')

You must use AND and not OR operator. Because you use NOT LIKE so, you must check either condition.

Upvotes: 0

Luca Basso Ricci
Luca Basso Ricci

Reputation: 18403

you have to use AND instead of OR in NOT LIKE condition

Upvotes: 0

Kermit
Kermit

Reputation: 34055

Change the second set's OR to an AND.

AND (client_name NOT LIKE 'P4%' AND client_name NOT LIKE 'P5%')

Upvotes: 3

Related Questions