|
It's simple three-valued Boolean logic - what's the problem?!
Chris Maunder wrote: IsNull(TableTwo.StringColumn, '') <> 'value'
Except that's not SARGabale.
WHERE
TableTwo.StringColumn Is Null
Or
TableTwo.StringColumn != 'value'
(You can remove TableTwo.TableTwoID Is Null , because if that's true, TableTwo.StringColumn will also be Null .)
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: It's simple three-valued Boolean logic
True, False, and Surprise.
cheers
Chris Maunder
|
|
|
|
|
True, False, and FileNotFound.
What Is Truth? - The Daily WTF[^]
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
And avoid "!=" in SQL
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Only if you're anal about standards-compliance.
Databases that support both != and <> :
- MySQL 5.1
- PostgreSQL 8.3
- SQLite
- Oracle 10g
- Microsoft SQL Server 2000/2005/2008/2012/2016
- IBM Informix Dynamic Server 10
- InterBase/Firebird
- Apache Derby 10.6
- Sybase Adaptive Server Enterprise 11.0
Databases that support the ANSI standard operator, exclusively:
- IBM DB2 UDB 9.5
- Microsoft Access 2010
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
0x01AA wrote: BTW: Have some Problems to seriously translate "if you're anal about compliance"
anal - Wiktionary[^] - definition #3: of a person, compulsive and stubborn, obsessed with neatness and accuracy
Synonyms: fussy, pernickety, picky
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Thank you for help me learning English. What such a insignifican word like "about" can change everything
Thank you
Bruno
modified 19-Jan-21 21:04pm.
|
|
|
|
|
Chris Maunder wrote: We're trying to find the number of records in TableOne which, when joined with TableTwo , either have no corresponding TableTwo row or the corresponding TableTwo row is not 'value'.
Either I've misunderstood your requirement, or that query isn't technically correct.
The query will find the records in TableOne which don't have any corresponding rows in TableTwo , and the records in TableOne which have at least one corresponding record in TableTwo where StringColumn is Null or not equal to 'value' . If there are multiple matching rows in TableTwo , then the rows from TableOne will be repeated.
Based on the description, I'd have thought something like this would be a better fit:
SELECT Count(1)
FROM TableOne
WHERE Not Exists
(
SELECT 1
FROM TableTwo
WHERE TableTwo.TableTwoID = TableOne.TableTwoID
And TableTwo.StringColumn = 'value'
)
That finds all the rows in TableOne which don't have a corresponding row in TableTwo with StringColumn set to 'value' .
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I think this version reads cleaner (and without the quirks), but I'd be willing to bet the non-sub select version would run quicker.
Jeremy Falcon
|
|
|
|
|
Jeremy Falcon wrote: would run quicker
Indeed.
#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
|
|
|
|
|
|
I feel ya man. NULL has always been a special case though. It's the bastard stepchild of values. Isn't technology fun?
Jeremy Falcon
|
|
|
|
|
This proves SQL is not a real programming language.
#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
|
|
|
|
|
Au contraire. It's the perfect language. Job security forever.
cheers
Chris Maunder
|
|
|
|
|
LOL. Especially when you have your own company!
#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
|
|
|
|
|
Quote: Job security forever Indeed. Writing script, backup,... It's boring but secure.
|
|
|
|
|
So is prostitution... but ya know...
Jeremy Falcon
|
|
|
|
|
|
Some are... so I'm told.
Jeremy Falcon
|
|
|
|
|
They don't all "enjoy" it. It's a job. Indeed, prostitutes go same-sex for their personal one-on-ones because opposite-sex has lost all appeal due to the "job".
#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
|
|
|
|
|
Sorry, You failed the interview. He'll call you next time.
|
|
|
|
|
So wait, you're saying I can't be a prostitute? Why? Because I'm a guy? That's discrimination.
Jeremy Falcon
|
|
|
|
|
Are you saying, Sql Dev's = prostitute?
|
|
|
|
|
Yes. Everyone knows you have go through prostitute training to get certified. You just finding this out? You may wanna talk to your MS rep.
Jeremy Falcon
|
|
|
|