Weirdest SQL Problem I've ever had...

[ibanez270dx]

Hello,
I am getting some craaazy results from an SQL statement in my Ruby on Rails application. I have two tables that each contain phone number information: "bes" and "imports". I am trying to display all the rows in "imports" that have a phone number that DOESN'T match any phone numbers in any rows in "bes". However, I keep coming up with zero results, although I know for sure that there should be results... Both columns are varchar(30) and the phone numbers are stored as 10 consecutive numbers. My SQL Statement is as follows:

note: prov_service_name is the username corresponding to the phone number and prov_service_number is that phone number. bes_phonenumber is phone number column in the "bes" table.

SELECT
imports.prov_service_number AS prov_num,
imports.prov_service_name AS prov_name
FROM imports
WHERE
imports.prov_service_number NOT IN ( select bes.bes_phonenumber from bes )

Now here is the kicker... This statement, doing simply the opposite of the statement above, works perfectly:

SELECT
bes.bes_displayname AS bes_name,
bes.bes_phonenumber AS bes_number,
FROM bes
WHERE
bes.bes_phonenumber NOT IN ( select imports.prov_service_number from imports )

Does anybody have any suggestions? This thing is starting to tick me off! Any help is appreciated!

Thanks,
- Jeff

[ibanez270dx]

Aha!!! I figured it out. NOT IN won't work if (in my case) import.prov_service_number has nulls. Thus, I had to use NOT EXISTS, as follows:

SELECT
imports.prov_service_number AS prov_num,
imports.prov_service_name AS prov_name
FROM imports
WHERE NOT EXISTS ( SELECT * FROM bes WHERE bes_phonenumber = imports.prov_service_number )


Thanks anyway, guys!