Where not in... - not working 1

[lisat76]

(I am reposting this here i originally posted it the ansi sql forum by mistake and was told to repost here)
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.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)

 

[Qik3Coder]

Have you tried selecting the P.term date, to verify that it is, in fact null?

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[SQLBill]

Try these two WHERE clauses (separately);

WHERE P.term_date = 'NULL'

WHERE P.term_date = ''

The first will 'catch' if you are using the word NULL and the second will catch if you are using a blank space instead of a NULL value.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[lisat76]

no that is not it if i totally remove the p.term date from the clause i still get no results

 

[CaptainD]

I'm wondering if there could be a white space issue since you are converting a text.

Try RTrim on the ID field.

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 [COLOR=blue]RTR
IM(s1.ID)[/color] 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)

 

[lisat76]

i thought that too, no go

 

[Qik3Coder]

What do you get when you run this in access on it's own?

select PRID from store.dbo.products as PD
inner join productinfo as P on P.PRid =PD.PRID  
where P.term_date is null

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[lisat76]

that table is not my access table that is my sql table
that works fine in sql

 

[Qik3Coder]

Sorry, i missed that the table wasn't in access.

Have you tried this without a subquery, as in doing a regular left join, where x.id is null?

Or else loading the data into a temp table, then running your query against the temp table?

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[lisat76]

ok so i tried to import the data to a temp table that worked still no results when using not in...
so I thought maybe the issue had something to do with converting the ID field etc.
SO for kick I changed the query to "where product not in
select productname from store.dbo.products"
Product name is just nvarchar for both tables

still no results even though i am 100% positive the test name i used is not in the database something odd is going on or i am just totally missing something.

 

[Qik3Coder]

Have you tried this without a subquery, as in doing a regular left join, where x.id is null?

SELECT  convert (varbinary(1000),s1.[ID]) as ID, s1.[product]
From OPENROWSET('Microsoft.Jet.OLEDB.4.0','d:\db\products\products.mdb';
'admin';'',products)as s1 
Left JOIN store.dbo.products as PD ON
	s1.ID = PD.PRID
LEFT join productinfo P on 
	P.PRid =PD.PRID  
WHERE
	PD.ID is null
	and P.term_date is null

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[CaptainD]

Something similar to this was suggested but I made a minor change using the IsNull() function.

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 RTR
IM(s1.ID) not in
   (select PRID from store.dbo.products as PD
inner join productinfo as P on P.PRid =PD.PRID  
where [COLOR=blue]IsNull(P.term_date, '') = '')[/color]

 

[lisat76]

thanks qikcoder3 that did the trick odd how where in did not like me

Have you tried this without a subquery, as in doing a regular left join, where x.id is null?

CODE
SELECT  convert (varbinary(1000),s1.[ID]) as ID, s1.[product]
From OPENROWSET('Microsoft.Jet.OLEDB.4.0','d:\db\products\products.mdb';
'admin';'',products)as s1
Left JOIN store.dbo.products as PD ON
    s1.ID = PD.PRID
LEFT join productinfo P on
    P.PRid =PD.PRID  
WHERE
    PD.ID is null
    and P.term_date is null

captainD what is the difference between the is null you did and this one?

 

[CaptainD]

IsNull() is a function

It changes NULL values to what ever you coded it for. In this case I was changing NULL to '' so that if you had NULL values and '' values they would both be covered.

You can also use it for Number data IsNull(SomeField, 0)

NULL values are now 0

so it's not the same as "where SomeField Is Null" which test only for NULL values

In your case I suggested it as a long shot that some of the fields were holding '' values