Help with NOT IN clause 1

[edwardturner]

Hi,

I've need to do a comparison between 2 fields in two 2 tables and identify the differences, I am using this SQL but it returns nothing:

select
field1+field2
from
table1
where field1+field2 NOT IN
(select field3+field4
from table2)

All the fields are nvarchar(100). There is definately differences but I do not understand why this statement does not return them, any ideaS?

 

[gmmastros]

You should left join the tables and look for NULLs.

Select Field1, Field2
From Table1
Left Join Table2
On Table1.Field1 = Table2.Field3
And Table2.Field2 = Table2.Field4
Where Table1.Field1 Is NULL



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[DotNetBlocks]

Hello,
I just ran this on my server and it worked.

SELECT     Field1 + Field2 AS Expr1
FROM         dbo.Table
WHERE     ((Field1 + Field2) NOT IN
                          (SELECT     Field1 + Field2 AS Expr1
                            FROM          dbo.Table
                            WHERE      (field2 <> "")))

I hope this helped.
Babloome

 

[vongrunt]

FYI this query compares two fields together. If Field3-Field4 contain variable-length data ambiguous join may happen. For example, '22'+'1' may be compared agains '2'+'21' and return false positive...

Plus concatenation (+) makes query slower, but I don't think that's an issue here.

Code posted by gmmastros has no such possible problems (without that obvious typo, should be WHERE Table2.Field1 IS NULL).

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)