homepage Welcome to WebmasterWorld Guest from 184.73.87.85
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
SQL syntax help
ahmed24




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

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

 

ahmed24




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

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%'
bhonda




msg:4530146
 12:23 pm on Dec 21, 2012 (gmt 0)

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.

ahmed24




msg:4531117
 12:39 pm on Dec 26, 2012 (gmt 0)

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
physics




msg:4531225
 10:43 pm on Dec 26, 2012 (gmt 0)

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%' ) )

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved