January 2, 2007 at 3:52 pm
I have two tables: A and B. A is the main table and B is the exception table. What I need to do is check if anything in Table A exists in table B based on a couple of fields.
Ex:
SELECT [sCustomerFullName], [sAccountNumber], [sSIM], [sPlanCode]FROM A a WHERE EXISTS (SELECT * FROM B b WHERE a.ssim = b.sSim AND a.sTMobilePlanCode = b.sTMobilePlanCode)
Let's say Table A contains custname(Joe Shmoe), but the sSIM is empty (because the table doesn't allow NULL values). This customer DOES NOT exist in table B. This record shouldn't be returned because it doesn't exist in table B, but it is being returned because the sSIM is empty.
I played with my own copy of the table A and set up sSIM as allowing NULL values. Using the query above did not return the record.
Does anyone know how to get around this if the field does not allow NULLs?
Thanks,
Ninel
January 2, 2007 at 4:07 pm
Can you check for something that will always have some data, maybe sAccountNumber. I think that not allowing NULL isn't your problem.
For example, if I have 30 people with the same sTMobilePlanCode and 15 of them have "empty" ssim, they are all going to match on an empty ssim in B. The reason that changing to NULL didn't return the person you were looking for is that comparing anything to NULL will always return false.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 3, 2007 at 3:51 am
I'm not sure I understand your problem correctly, but wouldn't checking for null do the trick? I mean like this:
SELECT [sCustomerFullName], [sAccountNumber], [sSIM], [sPlanCode]FROM A a WHERE EXISTS (
SELECT * FROM B b WHERE IsNull(a.ssim, '') = IsNull(b.sSim, '') AND IsNull(a.sTMobilePlanCode, '') = IsNull(b.sTMobilePlanCode, '')
)
It might be marginally slower, but it would work.
January 3, 2007 at 7:34 am
The problem is that the sSIM value is not null, it is an empty string. Is there any string function that can convert an empty string to a NULL so that I can compare?
January 3, 2007 at 7:56 am
Comparing to NULL is problematic even when you compare NULL to NULL.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 3, 2007 at 12:18 pm
Dont you have a PK on the tables?
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
January 3, 2007 at 2:21 pm
select nullif( columnname,'')
from your table
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply