Why does mySQL query give unexpected results?

Question

This is my mySQL query :

SELECT mCat,postSta 
FROM `info_posts` 
WHERE `mCat` = 'Mobiles' AND `sCat` = 'Mobile Phones' AND `brCat` = 'Apple' AND `postSta` = 1 OR `postSta` = 4 OR `postSta` = 5

The problem with this is that it selects all the criteria properly however it also fetches the things where postSta = 4 and 5 take a look at the screenshot. I want to select things which match the criteria of mCat,sCat and brCat where postSta is 1 or 4 or 5.

The problem is in the way the AND and OR conditions are being processed. — Nigel Ren, Dec 23 '18 at 20:12
Possible duplicate of [Mysql or/and precedence?](https://stackoverflow.com/questions/12345569/mysql-or-and-precedence) — Nick, Dec 23 '18 at 23:09

score 3 · Answer 1 · answered Dec 23 '18 at 20:11

3

Use IN:

SELECT mCat,postSta
FROM info_posts
WHERE mCat = 'Mobiles' AND sCat = 'Mobile Phones' AND brCat = 'Apple' AND 
      postSta IN (1, 4, 5)

The problem is that you need parentheses in your query, but IN is a better approach.

answered Dec 23 '18 at 20:11

Gordon Linoff

1,242,037
58
646
786

Thanks buddy !! That helped – user9514246 Dec 24 '18 at 17:05

score 2 · Accepted Answer · answered Dec 23 '18 at 20:13

2

The logical "and" operation has a higher precedence than the logical "or" operator. The logic you're looking for can be achieved by surrounding the series of "or" conditions with parenthesis:

SELECT `mCat`, `postSta`
FROM   `info_posts`
WHERE  `mCat` = 'Mobiles' AND
       `sCat` = 'Mobile Phones' AND
       `brCat` = 'Apple' AND
       (`postSta` = 1 OR `postSta` = 4 OR `postSta` = 5)

answered Dec 23 '18 at 20:13

Mureinik

297,002
52
306
350

Thanks buddy !! That helped – user9514246 Dec 24 '18 at 17:05

Why does mySQL query give unexpected results?

2 Answers2