Re: SQL Comparison of ISNULL values

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 07/15/04 

Date: Thu, 15 Jul 2004 12:23:34 -0400

Can you show the exact results you want?

Given this data:

CREATE TABLE table1
(
 ID INT,
 number INT
)
GO

CREATE TABLE table2
(
 ID INT,
 number INT
)
GO

SET NOCOUNT ON

INSERT table1 SELECT 1, 1
INSERT table1 SELECT 2, NULL
INSERT table1 SELECT 3, 5
INSERT table1 SELECT 4, 12
INSERT table1 SELECT 6, NULL

INSERT table2 SELECT 1, 5
INSERT table2 SELECT 3, NULL
INSERT table2 SELECT 4, 15
INSERT table2 SELECT 5, 12
INSERT table2 SELECT 6, NULL

Resultset:

??? 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"Dan Williams" <dan_williams@newcross-nursing.com> wrote in message
news:2eac5d02.0407150814.21685cab@posting.google.com...
> I'm attempting the following SQL query:-
>
>
> SELECT table1.id from table1 left outer join table2
> on table1.id = table2.id
> where ISNULL(table1.number,0) <> ISNULL(table2.number,0)
>
> But the damn thing won't work when either of the tables contain NULL
> values.
>
> How do i go about comparing two values even if one of them contains a
> NULL value? And i'd rather not have to create a huge query string!!
>
> Many thanks
>
> Dan Williams.

Relevant Pages

  • Filtering and adding rows based on some condition
    ... DECLARE @TABLE1 TABLE ... (USGID INT NOT NULL,PCID INT NOT NULL) ... DECLARE @TABLE2 TABLE ... USGID PCID PCDESC Status ...
    (microsoft.public.sqlserver.programming)
  • Re: Text column copy across tables
    ... existing rows in TABLE1 using data present in the text column of a ... corresponding row in TABLE2. ... create table t(i int, t text) ... Don't forget to set textsize if datalength of the textfield ...
    (comp.databases.sybase)
  • Re: Importing xml through tsql
    ... declare @h int ... --insert into table1 ... --insert into table2 ... > I want to upload xml data with sql's openxml. ...
    (microsoft.public.sqlserver.xml)
  • Re: ADP/SQL Data Selection Criteria in a Stored Procedure
    ... create table table1 (Client_Id int, DateEntered datetime, Employee_ID int) ... create table table2 ... > result in SP and return as a report row source in ADP. ...
    (microsoft.public.sqlserver.programming)
  • Re: Tricky Visual Basic Code help...
    ... moves the current record into a new record in table2. ... this with a little VBA code and a couple custom queries. ... Set a "pushFlag" column in the record in table1 to a specific ... If a duplicate is not found, ...
    (microsoft.public.access.formscoding)