SQL combining LIKE and AND gives me wrong result

Question

SELECT * FROM table WHERE column LIKE '%whatever%' AND id='1' OR id='2' OR id='3'

When using the code above, I get back three rows and not the one with
column LIKE '%whatever%'
I find it strange, and can not understand why.

How could I rewrite my sql code to use LIKE to check in sevral rows that have id='whatever'

I use MYSQL and MyISAM

Check out http://dev.mysql.com/doc/refman/5.7/en/operator-precedence.html — Franck Dernoncourt, Jun 24 '13 at 01:36
*I use MYSQL and MyISAM PROBLEM SOLVED* Uh no, that combination creates a problem. — ta.speot.is, Jun 24 '13 at 01:43
Please do not add `solved` to the question. Instead, accept an answer or post your own answer and accept it. — Alvin Wong, Jun 25 '13 at 12:52

score 6 · Answer 1 · answered Jun 24 '13 at 01:35

You need parentheses:

SELECT *
FROM table
WHERE column LIKE '%whatever%' AND (id='1' OR id='2' OR id='3')

AND has a higher precedence than OR, so your original was interpreted as:

WHERE )column LIKE '%whatever%' AND id='1') OR id='2' OR id='3'

The original is better written with in:

SELECT *
FROM table
WHERE column LIKE '%whatever%' AND id in ('1', '2', '3')

And, if id is really an integer, you should drop the single quotes. They suggest the type is really a string.

score 2 · Answer 2 · answered Jun 24 '13 at 01:37

2

Use the IN function

SELECT * FROM table WHERE column LIKE '%whatever%' AND id IN ('1', '2', '3')

I assume id is a varchar. If its an int then use:

SELECT * FROM table WHERE column LIKE '%whatever%' AND id IN (1, 2, 3)

answered Jun 24 '13 at 01:37

SyntaxGoonoo

2 Answers2