Welcome to WebmasterWorld Guest from 23.20.223.88

Forum Moderators: open

Message Too Old, No Replies

SQL syntax help

     
9:49 am on Dec 21, 2012 (gmt 0)

5+ Year Member



Need some help.

I have the following syntax that returns a set of results where the minutes field equals '60'


MINUTES IS NOT NULL AND MINUTES='60'


this works fine for me, however i also have another syntax below which checks exactly like above but for all the minutes fields that equal 30 and have a description field that does not have the word Test in it.


MINUTES IS NOT NULL AND MINUTES='30' AND DESCRIPTION NOT LIKE '%Test%'


This also works fine. But i am trying to figure out a way to combine these two into one syntax. Any ideas?

Thanks
10:02 am on Dec 21, 2012 (gmt 0)

5+ Year Member



I think i've figured it out. i combined the two with a OR in between them and it seems to be working. Is this the correct way?

My syntax now looks like this:

MINUTES IS NOT NULL AND MINUTES='60' OR MINUTES IS NOT NULL AND MINUTES='30' AND DESCRIPTION NOT LIKE '%Test%'
12:23 pm on Dec 21, 2012 (gmt 0)

10+ Year Member



Be careful with mixing AND and OR in the same statement without using brackets.

I would assume you want something like -

(MINUTES IS NOT NULL AND MINUTES='60')
OR
(MINUTES IS NOT NULL AND MINUTES='30' AND DESCRIPTION NOT LIKE '%Test%')

Because this is different and will return completely different results -

(MINUTES IS NOT NULL AND MINUTES='60' OR MINUTES IS NOT NULL)
AND
MINUTES='30' AND DESCRIPTION NOT LIKE '%Test%'

Using a mix of AND and OR in the same statement relies on them being interpreted in a specific order, and it's never clear just by looking at the statement how it's going to work.

It's like in maths, what is the result of -

3 + 5 * 2?

Depending on the order in which you make the calculations, the result is either 16 or 13.

Whereas using brackets, you can ask -

3 + (5 * 2)
or
(3 + 5) * 2

Likewise, make sure you always explicitly define what is being compared with AND and what is being compared with OR.
12:39 pm on Dec 26, 2012 (gmt 0)

5+ Year Member



Thanks for your reply.

My original single statement is already within brackets because there is AND statements before and after it like this:

AND (MINUTES IS NOT NULL AND MINUTES='60' ) AND


so, to combine both, will it need to be like this:

AND ((MINUTES IS NOT NULL AND MINUTES='60') OR (MINUTES IS NOT NULL AND MINUTES='30' AND DESCRIPTION NOT LIKE '%Test%') ) AND
10:43 pm on Dec 26, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Do you really need MINUTES IS NOT NULL when you're also specifying an amount for minutes? Seems unnecessary and confusing - unless for some reason you're finding that you need it I'd get rid of it. In that case this would be a simpler version of your query:

AND ( MINUTES='60' OR ( MINUTES='30' AND DESCRIPTION NOT LIKE '%Test%' ) )
 

Featured Threads

Hot Threads This Week

Hot Threads This Month