CODEWITHSUNDEEP
sqlms-accessvba
SScoder93
SScoder93

Reputation: 60

Comparison of a single field to two fields in SQL

I have a table in Microsoft Access which acts as a static list of countries(named country table), with a country_id field as a primary key identifier. I'm attempting to use country_id within the static list and compare them to another table (named references table) which hold country references (country_ref1 and country_ref2). Please note that the references table have duplicate entries within country_ref1 and country_ref2 and also include nulls/empty values. All fields are of the same type "Short/Text", and consist of two letters when populated.

If the country_id in the country table DOES NOT match country_ref1 AND country_ref2 in the references table, i'd like the country_id to be populated in my query.

This is what I've come up with so far, however it's not working (perhaps due to the "NOT IN" statement not accounting for nulls). I've also tried using LEFT JOIN & NOT EXISTS with no solution.

SELECT country_id FROM country WHERE country_id NOT IN (SELECT DISTINCT Country_ref1 FROM References) AND country_id NOT IN (SELECT DISTINCT Country_ref2 FROM References)

Any help would be appreciated. Thank you.

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use NOT EXISTS:

SELECT c.country_id
FROM country as c
WHERE NOT EXISTS (SELECT 1
                  FROM References r
                  WHERE c.country_id IN (Country_ref1, Country_ref2)
                 );

Upvotes: 3

Related Questions