sql server 2000 "in", "not in" 1

[tempo1]

Hi everyone,
I run a simple querry to view all rows where a values of column "contact" do not exist in another table.

select contact from final_aig
where
contact not in(select contact from aig_errors)
order by
lastname

but i get no results at all though there should be manu rows as a result
of that querry.
Am i using wrong code ? Is there "not in" in t-sql language ? I there is not, how do i get
the list o "contacts" that do not exist in the other table?
Thanks a lot.

 

[ilyad]

The SQL seems correct to me. Are you sure both fields are of the same type and length?

Another way to get the same result is:

select fg.contact, fg.LastName from final_aig fg left join aig_errors ae
on fg.contact = ae.contact where ae.contact is NULL
order by
fg.lastname

 

[tempo1]

Thanks a lot ilyad.
Since the code is correct, as you said, i chose another value (phone number) to categrize what rows i want to display.
Thanks.

 

[SQLDenis]

you have a null value in the column this is a common mistake
use exists or a left join instead or do this

select contact from final_aig
where
contact not in(select contact from aig_errors where contact is not null)
order by
lastname

I would prefer NOT EXISTS myself

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[tempo1]

Thanks a lot !