AND/OR in WHERE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

AND/OR in WHERE

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-10-03 : 09:34:23

I was just asked to explain a piece of code and I usually don't have a problem with that sort of thing but this one had me wondering! Somebody mad the query using the query designer and it's a complete mess but I've broken things down a bit and have come to this final piece of code that I can't really get my head around:

...
WHERE interview.id is null
and (case.case_item is null or case.case_item = 'dbv')
or (interview.id is null)
and case.case_item in ('dub', 'dim')
and case.interview_office is null
and case.til = -1

I have obfuscated the table/column names but it is this OR in combination with the case.case_item's that really has me confused and I can't seem to get the presedence of things. Can any of you guys rewrite this thing in to proper a more proper where-clause that is actually understandable??

- Lumbago
My blog-> http://thefirstsql.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-03 : 09:50:50

Just by observation, it seems to be the following, but more often than not, I don't trust my observations without a lot of testing.

WHERE
	interview.id IS NULL
	AND
	( 	
		case.case_item IS NULL 
		OR 
		case.case_item = 'dbv'
		OR
		(
			case.case_item IN ('dub', 'dim') 
			AND 
			case.interview_office IS NULL 
			AND 
			case.til = -1
		)
	)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-10-04 : 04:28:18

That is just awesome...right on the money! Thank you so much

I'm not so sure that the logic behind this was intentional or not but nevertheless you decoded it perfectly. I don't think I would have gotten there at all actually hehe...

- Lumbago
My blog-> http://thefirstsql.com

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-04 : 07:07:05

quote:
I don't think I would have gotten there at all actually

You are being way too modest!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources