Look for matching and unmatching fields on a same table in Access

Question

I'm having some trouble with Access. I have a PHONES table with two very specific columns: Provider_prefix and client_prefix. I need to be able to generate a query that will give me every provider_prefix that is not on client_prefix (that is, there are no clients in the prefix area of that provider), and one that will do just the opposite: give me the prefixes that match. So far I've tried the following:

SELECT * 
FROM PHONES INNER JOIN PHONES_2
ON PHONES.provider_prefix != PHONES_2.client_prefix;

Which will just fail. I've also tried the following:

SELECT *
FROM PHONES
WHERE provider_prefix NOT IN (SELECT DISTINCT client_prefix
                                FROM PHONES)

Which, while technically working, throws zero, and my guess is that is happens because there are nulls in the columns (which are both Number-type). Any ideas would be very appreciated.

Use `Nz(client_prefix,0)` and `Nz(provider_prefix,0)`. If 0 is a possible actual value use -1 or -999 or anything that would otherwise be impossible to encounter and want to look for. You may also want to use two different values if you don't want equality to trigger when both fields are `null`. — SunKnight0, Mar 06 '18 at 15:44

score 0 · Accepted Answer · answered Mar 06 '18 at 20:30

A NOT IN clause tends to deal poorly with Null values (see this question).

The usual way to avoid this, is to rewrite to a NOT EXISTS clause.

SELECT *
FROM PHONES p
WHERE  NOT EXISTS (SELECT 1
                    FROM PHONES s
                    WHERE s.client_prefix = p.provider_prefix)

Look for matching and unmatching fields on a same table in Access

1 Answers1