Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Databases _ NULL problem in SQL Server

Posted by: DeaPeaJay Feb 14 2007, 12:20 PM

I've brainstormed and researched this problem but I just can't seem to figure it out.

This is my SQL

CODE

select name, title, department, about, notes from Providers
where name != ANY
(select provider from needProviders
where needID = 1)

pretty straightforward. the provider column is a foreign key to the name column. I'm trying to remove from the result set the names that are in the inner query. It works great! Just as I expect it would, unless the inner query returns an empty result set. Then, it doesn't return anything at all, when it *should* return everything, since name is not equal to an empty result set.

I'm just confused as to what SQL Server is doing, I suspect that I wouldn't have this problem with mySQL. Or am I missing something obvious?

Posted by: DeaPeaJay Feb 14 2007, 12:55 PM

Ok, that's just weird. I used "not in" instead of "!= ANY" and now it works. Logically I don't see why that would make a difference but it did.