CODEWITHSUNDEEP
sqlsql-server
Joeseph Schmoe
Joeseph Schmoe

Reputation: 312

SQL - Match value if exists; else match NULL

I have an SQL table:

country state county city
'us' 'ny' 'steuben' NULL
'us' 'ny' 'steuben' 'city a'
'us' 'ny' 'steuben' 'city b'
'us' 'ny' NULL NULL
'us' NULL NULL NULL
NULL NULL NULL NULL

The goal is to have some sort of query where "country, state, county, and city" are always supplied, but what is provided may not exist in the table. If a supplied attribute isn't found, I would like the relevant 'NULL' value from the column.

Example: User provides "us, ny, steuben, city b" --> Row #3

Example: User provides "us, ny, steuben, city c" --> Row #1 because 'city c' is unknown

Example: User provides "us, ny, hamilton, city a" --> Row #4 because 'hamilton' is unknown

The original thought was:

SELECT * 
FROM [location] 
WHERE 
   (country = @country OR country IS NULL) AND
   ([state] = @state OR [state] IS NULL) AND
   (county = @county OR county IS NULL) AND
   (city = @city OR city IS NULL)

However, this will return the NULL rows indiscriminately. What I'm really looking for is some sort of logic like:

SELECT * 
FROM [location] 
WHERE 
   (country = @country IF EXISTS OR country IS NULL) AND
   ([state] = @state IF EXISTS OR [state] IS NULL) AND
   (county = @county IF EXISTS OR county IS NULL) AND
   (city = @city IF EXISTS OR city IS NULL)

Does anyone have a potential solution?

Note: If the answer varies with databases, I'm interested in MS-SQL.

Upvotes: 1

Views: 2643

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use filtering and order by:

select top (1) t.*
from t
where (t.country = @country or t.country is null) and
      (t.state = @state or t.state is null) and
      (t.county = @county or t.county is null) and
      (t.city = @city or t.city is city)
order by ( (case when t.country = @country then 1 else 0 end) +
           (case when t.state = @state then 1 else 0 end) +
           (case when t.county = @county then 1 else 0 end) +
           (case when t.city = @city then 1 else 0 end)
         ) desc;

The key is to order by the number of full matches descending.

Upvotes: 2

Related Questions