Robert Blasco Villarroya Robert Blasco Villarroya - 5 months ago 14
MySQL Question

OR conflict between other conditions

I have the next query:

SELECT * FROM `Contacts`
WHERE `Zona` = '1'
AND `Responsable` = '9'
AND `AllowComercialVisit` = 'Call_Again'
-- the problem are OR's --
OR `AllowComercialVisit` = 'Busy'
OR `AllowComercialVisit` = 'Not_answered'
-- the problem are OR's --
AND `DateRecall` <= '2016-06-20 13:04:52'
AND `DateRecall` >= '2016-06-20 12:39:52'
ORDER BY `DateRecall` ASC LIMIT 1


The problem is that the query should show ONLY the rows between first and second 'DateRecall' but return all the rows with 'Call_Again','Busy' and 'Not_answered'.

Any solution?

Answer

A simple IN() would solve this:

SELECT * FROM `Contacts` 
WHERE `Zona` = '1' 
    AND `Responsable` = '9' 
    AND `AllowComercialVisit` IN('Call_Again','Busy','Not_answered') 
    AND `DateRecall` <= '2016-06-20 13:04:52' 
    AND `DateRecall` >= '2016-06-20 12:39:52' 
ORDER BY `DateRecall` ASC LIMIT 1

In general, AND has precedence over OR , when using OR try using parentheses ->

WHERE COND1 AND COND2 AND(COND3 OR COND4) AND COND5

Which will force the optimizer to follow your precedence and not the default one.

Comments