homepage Welcome to WebmasterWorld Guest from 54.211.230.186
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
SQL syntax help
ahmed24

5+ Year Member



 
Msg#: 4530134 posted 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

5+ Year Member



 
Msg#: 4530134 posted 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

5+ Year Member



 
Msg#: 4530134 posted 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

5+ Year Member



 
Msg#: 4530134 posted 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

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4530134 posted 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.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved