CRM - Query records with 'incompatible' combinations of values (SQL, SSRS)

Question

I have a record type in my CRM environment where there are two lookups. There are certain combinations of values that aren't "valid," but no constraint in the system to prevent that entry. To illustrate, I'll use a small sample of cities and states to explain.

External to the CRM, I have a spreadsheet to show what combinations of cities and states are okay and which aren't; for example, Boston is a city in MA, but not in MN, TX, or WI. Arlington, however, is a city in all four states, etc.:

The problem is there's nothing stopping someone from putting "Cambridge" for the city and "TX" for the state, for example. My hope was to somehow use this spreadsheet's data as an input combined with a SQL query to create an SSRS report that would show records with incompatible combinations:

SELECT name, city, state
FROM contacts
WHERE [magic for the query to know there's no Cambridge in Texas, etc.]

I could go through and define each of the combinations as valid or not in the query itself, but something tells me there's a more efficient way to do this instead of defining the several hundred possible combinations that I have.

I'm working on configuring the fields so valid entry is forced, but I need to identify and clean up the existing entries.

Answer 1

You'll need a table of valid combinations. If you don't already have one, then you'll need to create one. The simplest form would be something like

CREATE TABLE CityState(City varchar(50), State varchar(2))

INSERT INTO CityState VALUES
('Boston', 'MA'),
('Arlington', 'MN'),
('Arlington', 'MA'),
('Arlington', 'TX'),
('Arlington', 'WI')

etc...

Once you have this, your query would be something like

SELECT c.name, c.city, c.state,
       CASE s.City WHEN NULL THEN 0 ELSE 1 END AS IsValid
    FROM contacts c
        LEFT JOIN CityState s ON c.city = s.City AND c.state = s.State