Returnig records on EXIST

ninel · January 2nd, 2007, 06: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:

Code:

                                                                                                                                                                       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

Jeff Moden · January 2nd, 2007, 08:17 PM

Why not do just an inner join?

Also, if you don't mind... please preview your posts and make sure that we can actually see all of your code without having to scroll to the right so far. Thanks.

--Jeff Moden