Selecting with lots of AND/ORs

Echilon

I have table which contains two values, start date and end date. It also contains a category field, which can be one of a few values. I want a query that selects everything between $startdate and $enddate but only returns results where the category is equal to $cat1 or $cat2.

Thye query I have at the minute isn't working:
SELECT * FROM events WHERE start BETWEEN '1140652800' AND '1140739199' AND category = 'General' OR category = 'Meetings'

It's as though I need to put brackets around it if I could, because it's treating it as:
WHERE start BETWEEN '1140652800' AND '1140739199' AND category = 'General'
OR
category = 'Meetings'

So it returns alot more than it should do, returning everything from the 'Meetings' category regardless of the date.

frost110

Try this:

SELECT * FROM `events` WHERE (`start` BETWEEN '1140652800' AND '1140739199' AND `category` = 'General') OR `category` = 'Meetings'

--FrosT

Roger_Ramjet

You can put brackets around things, infact you have to if you want to group conditions. Read up about Operator Precedence to see what the default order of multiple conditions would be, and what you therefore have to coerce through bracketting. You should be able to work the rest out from there, infact you already have in your question but just don't know it.

And Frost's answer is wrong, but I know he knows better so he must have misunderstood your question.

hint: OR takes precedence over AND - it gets evaluated first - which is why you are getting everything where category = 'Meetings'.

PS, backticks around table and column names are only neccessary if the names are non-standard, or reserved words. Since you should never use such things for names you should never need backticks, so loose them cos they make me shudder 😉

Echilon

Thanks for the help, I've never needed brackets on SQL statements before, but there's a first time for everything 😃

I read somewhere that backticks cause a slowdown in execution time aswell, but I don't know how true that is.