Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Views and NULLS 1

Status
Not open for further replies.
Aidy680

Aidy680

Technical User
Nov 1, 2006
138
GB
I have a SQL View that joins two tables on 4 fields.

Three of the 4 fields, in both tables, are populated. The fourth isnt. They contain NULL values instead.

I only want to return records that DONT exist in both tables.

The problem is that the view doesn't recognise the NULL values as matching, and so the record is returned, when I'd expect it to be excluded.

If I remove this 4th JOIN, the View works and the record is excluded from the results. However this isnt an option.

I'm currently using this code within the WHERE clause (cutdown version):

(KX.Field1 <> FX.Field1) OR
(KX.Field1 IS NULL) AND (FX.Field1 IS NOT NULL) OR
(KX.Field1 IS NOT NULL) AND (FX.Field1 IS NULL)

This seems to work fine if one side is a NULL, but not both.

I didnt think this would be too difficult...!!?
 
Sort by date Sort by votes
Code: 
(ISNULL(KX.Field1,'') <> ISNULL(FX.Field1,'))

Just change [''] to the proper type of the filed.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Thanks Borislav but this hasn't worked, even though I can see the logic behind it.

I've posted the rest of the SQL below if it's any help at all:

SELECT FX.DateFirstAppeared, FX.Field1, FX.Field2, FX.Field3, FX.Field4,
FX.Field5, FX.Field6

FROM dbo.tblFirstStageExceptionsSystem3 AS FX LEFT OUTER JOIN
dbo.tblKnownExceptionsSystem3 AS KX ON FX.Field4 = KX.Field4 AND FX.Field3 = KX.Field3 AND
FX.Field2 = KX.Field2 AND FX.Field1 = KX.Field1

WHERE (KX.Field1 IS NULL) OR

(KX.Field3 <> FX.Field3) OR
(KX.Field3 IS NULL) AND (FX.Field3 IS NOT NULL) OR
(KX.Field3 IS NOT NULL) AND (FX.Field3 IS NULL) OR
(KX.Field2 <> FX.Field2) OR
(KX.Field2 IS NULL) AND (FX.Field2 IS NOT NULL) OR
(KX.Field2 IS NOT NULL) AND (FX.Field2 IS NULL) OR
(ISNULL(KX.Field4, 'Test') <> ISNULL(FX.Field4, 'Test'))
 
Upvote 0 Downvote
No this is ALL:
Code: 
SELECT     FX.DateFirstAppeared, FX.Field1, FX.Field2, FX.Field3, FX.Field4,
                      FX.Field5, FX.Field6

FROM         dbo.tblFirstStageExceptionsSystem3 AS FX LEFT OUTER JOIN
                      dbo.tblKnownExceptionsSystem3 AS KX ON FX.Field4 = KX.Field4 AND FX.Field3 = KX.Field3 AND
                      FX.Field2 = KX.Field2 AND FX.Field1 = KX.Field1

WHERE  (ISNULL(KX.Field4, 'Test') <> ISNULL(FX.Field4, 'Test'))

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
OOOOPS!!!
I answered before I read the whole query.

Here:
Code: 
SELECT     FX.DateFirstAppeared,
           FX.Field1,
           FX.Field2,
           FX.Field3,
           FX.Field4,
           FX.Field5,
           FX.Field6

FROM dbo.tblFirstStageExceptionsSystem3 AS FX
LEFT JOIN dbo.tblKnownExceptionsSystem3 AS KX 
         ON FX.Field4 = KX.Field4 AND FX.Field3 = KX.Field3 AND
            FX.Field2 = KX.Field2 AND FX.Field1 = KX.Field1

WHERE KX.Field2 IS NULL

The logic is this:
You joined these two tables on all fields you need and they need to be equal.
So if you have records in FX where some of these fields didn't match the fields in KX then all KX fields will be NULL.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Thanks again Borislav for your quick reply but this doesn't work either as, although it excludes the record that I wanted it too originally, it's now also suppresing other records, which SHOULD be included.

Is the LEFT OUTER JOIN confusing the matter?
 
Upvote 0 Downvote
Boris the logic is as per my original post.

I need to retain the criteria around the other fields.

The fourth field doesn't always contain NULLs, if that's confusing the matter at all.

SQL doesn't recognise two NULLS as matching.

I appreciate your help with this.

 
Upvote 0 Downvote
Can you show some sample data and expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
Code: 
[COLOR=blue]SELECT[/color]     FX.DateFirstAppeared,
           FX.Field1,
           FX.Field2,
           FX.Field3,
           FX.Field4,
           FX.Field5,
           FX.Field6
[COLOR=blue]FROM[/color] dbo.tblFirstStageExceptionsSystem3 [COLOR=blue]AS[/color] FX
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] dbo.tblKnownExceptionsSystem3 [COLOR=blue]AS[/color] KX
         [COLOR=blue]ON[/color] [COLOR=#FF00FF]ISNULL[/color](FX.Field4,[COLOR=red]'Test'[/color]) = [COLOR=#FF00FF]ISNULL[/color](KX.Field4,[COLOR=red]'Test'[/color]) AND 
            [COLOR=#FF00FF]ISNULL[/color](FX.Field3,[COLOR=red]'Test'[/color]) = [COLOR=#FF00FF]ISNULL[/color](KX.Field3,[COLOR=red]'Test'[/color]) AND
            [COLOR=#FF00FF]ISNULL[/color](FX.Field2,[COLOR=red]'Test'[/color]) = [COLOR=#FF00FF]ISNULL[/color](KX.Field2,[COLOR=red]'Test'[/color]) AND
            [COLOR=#FF00FF]ISNULL[/color](FX.Field1,[COLOR=red]'Test'[/color]) = [COLOR=#FF00FF]ISNULL[/color](KX.Field1,[COLOR=red]'Test'[/color])
[COLOR=blue]WHERE[/color] KX.Field2 [COLOR=blue]IS[/color] NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
gmmastros the below is the record I want to exclude as Field4 is NULL in both tables. However, this record appears in my final results:

Date Field1 Field2 Field3 Field4 Field5 Field6
19/07/2010 9207909 ECGL -4564000 NULL NULL NULL

Conversley, the record below is a legit inclusion within my results as Field3 and Field6 are different and are joined in my query:

Date Field1 Field2 Field3 Field4 Field5 Field6
19/07/201 9098170 PASH 3536168 9098170 PASH 3536169

Note that Field6 is the same as Field3, albeit in the KX table ie.

(KX.Field3 <> FX.Field3) OR

I hope this makes sense!
 
Upvote 0 Downvote
O! GOD!!!!!!!
Till now I thought you need ALL fields to match and even one of them is different you need the result.
If you need a result if ONE of fields match then you need OR not AND in JOIN condition:
Code: 
[COLOR=blue]SELECT[/color]     FX.DateFirstAppeared,
           FX.Field1,
           FX.Field2,
           FX.Field3,
           FX.Field4,
           FX.Field5,
           FX.Field6
[COLOR=blue]FROM[/color] dbo.tblFirstStageExceptionsSystem3 [COLOR=blue]AS[/color] FX
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] dbo.tblKnownExceptionsSystem3 [COLOR=blue]AS[/color] KX
         [COLOR=blue]ON[/color] [COLOR=#FF00FF]ISNULL[/color](FX.Field4,[COLOR=red]'Test'[/color]) = [COLOR=#FF00FF]ISNULL[/color](KX.Field4,[COLOR=red]'Test'[/color]) OR
            [COLOR=#FF00FF]ISNULL[/color](FX.Field3,[COLOR=red]'Test'[/color]) = [COLOR=#FF00FF]ISNULL[/color](KX.Field3,[COLOR=red]'Test'[/color]) OR
            [COLOR=#FF00FF]ISNULL[/color](FX.Field2,[COLOR=red]'Test'[/color]) = [COLOR=#FF00FF]ISNULL[/color](KX.Field2,[COLOR=red]'Test'[/color]) OR
            [COLOR=#FF00FF]ISNULL[/color](FX.Field1,[COLOR=red]'Test'[/color]) = [COLOR=#FF00FF]ISNULL[/color](KX.Field1,[COLOR=red]'Test'[/color])
[COLOR=blue]WHERE[/color] KX.Field2 [COLOR=blue]IS[/color] NULL

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.
 
Upvote 0 Downvote
Thanks Boris it was the ISNULL in the 'ON' clause that did the trick.

I also found using the 'EXCEPT' keyword in a proc achieved the same result.

Thanks for your efforts with this. Very much appreciated.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
582
pjw001
pjw001
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
546
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
347
Mike Lewis
Mike Lewis
grnzbra
  • Locked
  • Question
T-SQL Dialog Box
Replies
8
Views
321
Chris Miller
Chris Miller
Andrzejek
  • Locked
  • Question
Eliminate pesky [ ] in SQL Management Studio
Replies
5
Views
390
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top