Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Simple SQL Select question

Posted on 2007-04-09
2
Medium Priority
?
226 Views
Last Modified: 2013-12-24
I have a table that has a RecStatus field in it.  For certain record types, the RecStatus is empty.
When I run the sample queries below, only the records with a RecStatus <> Imported or Pursuing are returned. (ie. RecStatus=Maintaining or New are returned)  The records where RecStatus is empty are also not returned.  Can someone tell me why?  Can someone tell me how to fix?  I need the empty RecStatus records as well.

Sample 1:
<cfquery name="sqlAccounts" datasource="#Request.SomeDSN#">
SELECT * FROM tblAccts WHERE RecStatus <> 'Imported' AND RecStatus <> 'Pursuing'
</cfquery>
Sample 2:
<cfquery name="sqlAccounts" datasource="#Request.SomeDSN#">
SELECT * FROM tblAccts WHERE RecStatus NOT IN('Imported','Pursuing')
</cfquery>
0
Comment
Question by:sulzener
2 Comments
 
LVL 3

Accepted Solution

by:
JipFromParis earned 2000 total points
ID: 18878038
I assume the RecStatus column allow NULL values. NULL values never match any predicate in a WHERE clause, albeit the IS NULL predicate. So, you should rewrite the first request as :

SELECT * FROM tblAccts WHERE (RecStatus <> 'Imported' AND RecStatus <> 'Pursuing') OR (RecStatus IS NULL)
0
 

Author Comment

by:sulzener
ID: 18878550
Thanks ... Not sure that has ever registered with me before.  Thanks for clearing that up.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question