Hi guys wonder if you can help. I am writing a query to return search results. The trouble is some of the critera returns invalid results. I think I need to put some () around the statement, but dont know where can someone help?
SELECT * FROM tblmilkers A
INNER JOIN tbldates B ON A.ID = B.milkerID
WHERE B.date BETWEEN '2009-05-17 00:00:00' AND '2009-08-30 23:59:59'
AND A.nearestTown = 143 OR A.otherWorkableTowns LIKE '|143|'
AND B.status = 1 AND shedPreference = 'Rotary' AND skillSet = 'Sole Charge or Assistant' AND milkingExperience = '0-1 years' AND age = '18 and under' ORDER BY B.date, A.ratePerMilking ASC
WHERE B.date BETWEEN '2009-05-17 00:00:00'
AND '2009-08-30 23:59:59' -- this is c1
AND A.nearestTown = 143 -- this is c2
OR A.otherWorkableTowns LIKE '|143|' -- this is c3
AND B.status = 1 -- this is c4
AND shedPreference = 'Rotary' -- this is c5
AND skillSet = 'Sole Charge or Assistant' -- this is c6
AND milkingExperience = '0-1 years' -- this is c7
AND age = '18 and under' -- this is c8
so this is equivalent to --
WHERE c1 AND c2 OR c3 AND c4 AND c5 AND c6 AND c7 AND c8
okay, now, ANDs take precedence over ORs, so this is evaluated as follows --
WHERE ( c1 AND c2 )
OR ( c3 AND c4 AND c5 AND c6 AND c7 AND c8)
however, i'd be willing to bet that what you want is this --
WHERE c1 AND ( c2 OR c3 ) AND c4 AND c5 AND c6 AND c7 AND c8
tip: when mixing ANDs and ORs, always code your own parentheses to get the logical evaluation you need