Where not in... - not working

[lisat76]

I have a SQL database table that i import data into from an access database. The id in the sql DB is varbinary while the id in the access db is text so i convert the text to varbinary in my query. That seems to work.
When i have an issue is when i do a "not in" in the where clause. I do this so if the product is already in the db i don't want it imported again.
However if i add a where clause i get no results even though i am positive the ID is not in the sql database.
MAtter of fact if i change the where to be "IN" instead of "not in" I still get no results.
I have done queries like this several times with no issues, I must be missing something in my syntax. If i just run the query without the where clause it works just fine.
Here is my syntax

SELECT  convert (varbinary(1000),[ID]) as ID,[product]
From
OPENROWSET('Microsoft.Jet.OLEDB.4.0','d:\db\products\products.mdb';
'admin';'',products)as s1 where s1.ssn not in
   (select PRID from store.dbo.products as PD
inner join productinfo as P on P.PRid =PD.PRID  
where P.term_date is null)

 

[SQLSister]

are you sure ssn and prid are the same thing? Wouldn;t you want where ID not in?

"NOTHING is more important in a database than integrity." ESquared

 

[lisat76]

oh i made a mistake typing the query here but it is id in my real query here is is again

SELECT  convert (varbinary(1000),[ID]) as ID,[product]
From
OPENROWSET('Microsoft.Jet.OLEDB.4.0','d:\db\products\products.mdb';
'admin';'',products)as s1 where s1.ID not in
   (select PRID from store.dbo.products as PD
inner join productinfo as P on P.PRid =PD.PRID  
where P.term_date is null)

 

[r937]

perhaps re-post the question in the SQL Server forum?

this is the ANSI SQL forum

r937.com | rudy.ca