Mysql query doesn't follow condition

Question

So I have the following mysql query, which works but not the right way:

SELECT *
FROM (`CriteriaItems`)
INNER JOIN `Address` 
  ON `Address`.`address_id` = `CriteriaItems`.`address_id`
WHERE `criteria_id` = '2'
  AND `status` = 'published'
  AND `mls_id` LIKE '%123%'
  OR `Address`.`address` LIKE '%123%'
LIMIT 10

The problem is that I see it returns results that also have "status" = "deleted" and thats because of ``Address.addressLIKE '%123%' if I remove this it works, but I need this.

So it suposed to search in the address table as well, but only based on CriteriaItems`.`address_id

You need parentheses in the `WHERE` clause. Or, perhaps `AND` instead of `OR`. — Gordon Linoff, Nov 08 '15 at 16:35

score 3 · Accepted Answer · edited May 23 '17 at 11:51

Its because AND operator has higher precedence than OR, right now your query works like this.

WHERE `criteria_id` = '2' 
AND (`status` = 'published' AND `mls_id` LIKE '%123%') 
OR `Address`.`address` LIKE '%123%' LIMIT 10

More information on this can be found here

So you need to use Parenthesis to apply the condition properly

WHERE `criteria_id` = '2' 
AND `status` = 'published' AND 
(`mls_id` LIKE '%123%' OR `Address`.`address` LIKE '%123%')
LIMIT 10

Lukasz Szozda · Answer 2 · 2015-11-08T16:35:43.460

1

You need to wrap OR with bracket:

SELECT * 
FROM `CriteriaItems`
JOIN `Address` 
  ON `Address`.`address_id` = `CriteriaItems`.`address_id` 
WHERE `criteria_id` = '2' 
  AND `status` = 'published' 
  AND (`mls_id` LIKE '%123%' OR `Address`.`address` LIKE '%123%')
LIMIT 10;

Consider also using ORDER BY to get stable results.

Simple bool logic:

p1 AND p2 AND p3 OR p4 is true <=> (p1 AND p2 AND p3) is true OR p4 is true

I guess you want:

p1 AND p2 AND (p3 OR p4)

edited Nov 08 '15 at 16:35

answered Nov 08 '15 at 16:35

Lukasz Szozda

162,964
23
234
275

1

You should add an explanation as to *why* this is the solution – John Conde Nov 08 '15 at 16:35

Mysql query doesn't follow condition

2 Answers2