?
Solved

MS SQL Where ID <> 3 - NOT Showing where ID is NULL

Posted on 2012-08-20
3
Medium Priority
?
575 Views
Last Modified: 2012-08-20
Hello All,

I'm confused, I've come across an issue I've not experienced before.

I have a table with columns including ID, ParentID, the first record (ID 1) does not have a ParentID so it is a NULL column, there will be other records in the table where ParentID is NULL.

I'm trying to select everything from the table; that works fine.... BUT when I try to filter it by only selecting all rows where ParentID is not equal to 3, it filters out all of the rows with a null value.

This is wrong as it isn't what i wanted it to do, I wanted everything except the records with a ParentID of 3, not everything except the records with a ParentID of NULL or 3....

Here's my query --

Select
P.ID,
ISNull(ParentID,0)ParentID,
Title,
PageType
from dbo.MyPage P
where ParentID <> 3
order by P.ID

Open in new window


What have i missed here?

Thanks
0
Comment
Question by:garethtnash
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38311472
>BUT when I try to filter it by only selecting all rows where ParentID is not equal to 3, it filters out all of the rows with a null value.

If you want to retain your ParentID IS NULL values, then use COALESCE to change all NULLs to something other than 3, like this:

WHERE COALESCE(ParentID, 0) <> 3
0
 

Author Closing Comment

by:garethtnash
ID: 38311475
Thanks Jim
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38311482
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

862 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