perplexed why a not in does not work

[timscronin]

I usually use the not in clause in place of a left outer join and null to find records not in a table. I have table called table A which has all records. Table B needs to be populated with any new ones added to A. When I try to use a NOT IN sub select to determine this it returns no records. The fields which are the linking fields are both
varchar(6). Below is the NOT IN and the left outer join, any ideas why it doesn't work?

select * from a where otheridnumber
not in (select inmateid from b)

select * from a left outer join
b
on b.inmateid = a.otheridnumber
where b.inmateid is null

 

[JayKusch]

Does your otheridnumber and inmateid have characters in them or are they strictly numeric?

Thanks

J. Kusch

 

[timscronin]

they are numeric , I did try converting them to int and still had the same issue

 

[Mr3Putt]

try:

AND (NOT (otheridnumber in (select inmateid from b)))

I don't think "not in" is a valid statement...

You need to know where "otheridnumber DOES match an inmateid", then NOT select it.

 

[JayKusch]

SO ....

select *
from a
where CONVERT(Int,otheridnumber)
not in (select CONVERT(Int,inmateid) from b)

works but No records are returned????

IF NOT, are you sure there are records in TableA that are not in TableB or maybe you have your tables reveresed.

By the way ... "NOT IN" is valid syntax.



Thanks

J. Kusch

 

[timscronin]

There are actaully records in one and not the other, if I did the not null I got records and verified it. Probably will call Microsoft

 

[gfjzr]

timscronin, the key words "NOT IN" will not work when you are dealing with nulls.
The better method is:

select a.* from a
where not exist(select * from a join b on a.otheridnumber = b.inmateid )

Let me know your results.

Gary