How to Use NOT IN

Hi,

Im having a view(View1).Its unique keys are nvcrLocationCode, nvcrAssetCode.

Theres another table (Table1) thats also contain same unique keys.

I just want to filter records from View1 which are not in Table1.

Hint:

View1

nvcrLocationCode nvcrAssetCode

H001 A0001

W001 A0001

Table1

nvcrLocationCode nvcrAssetCode

H001 A0001

It should out put

W001 A0001

How do I do this

You could use EXCEPT operator (in SQL SERVER 2005 only) or NOT EXISTS clause:

Code Snippet

create table #View1

(

nvcrLocationCode nvarchar(4),

nvcrAssetCode nvarchar(5)

)

go

create table #Table1

(

nvcrLocationCode nvarchar(4),

nvcrAssetCode nvarchar(5)

)

go

select * fro

insert into #View1 values('H001', 'A0001')

insert into #View1 values('W001', 'A0001')

insert into #Table1 values('H001', 'A0001')

select * from #view1

EXCEPT

select * from #Table1

select * from #view1 v where NOT EXISTS

(

select * from #table1 t

where t.nvcrLocationCode=v.nvcrLocationCode and

t.nvcrAssetCode=v.nvcrAssetCode

)

|||you can also try this..

SELECT View1.*
FROM View1 LEFT OUTER JOIN
Table1 ON View1.nvcrLocationCode = Table1.nvcrLocationCode
AND View1.nvcrAssetCode = Table1.nvcrAssetCode
WHERE Table1.nvcrLocationCode IS NULL
AND Table1.nvcrAssetCode IS NULL