Constructing SQL query(ies) with optional parameters/ranking

Question

Hi all,

I was wondering what would be a good way to accomplish the following using mysql:

Lets say I have a table that contains these fields:

MyTable
---------
Country [string]
Region/Province/State [string]
City [string]

And I have the following data rows in the database

Entry 1: Canada, Ontario, Toronto
Entry 2: Canada, Ontario, Hamilton
Entry 3: Canada, Alberta, Calgary

Now I want to be able to search that table based on user supplied information, However, if there are no results found with the user's supplied information I want the program to try and make it less specific. For example, if the user supplies:

Canada, Ontario, Kingston

I would like the search query to search for all 3 fields (which would produce 0 rows), then just for the country/region (which would produce 2 rows), and then just for the country (which should produce only 1 extra row on top of the previous two). Is that possible and, if it is, would it be fairly efficient ? Can this be done with 1 query or would it require multiple queries and then some cross-referencing to eliminate identical rows (I imagine that wouldn't be very efficient) ?

Thank you very much!

Edit By cross-referencing/multiple queries I was thinking about using UNION with several selects. But I was wondering if there is a better/more logical way to do this.

Answer 1

I would go with the 3 queries.

Normalize the table so you can check on small tables for country, region/province/state and city and then link them to the actual entries on a 1 -> N (one to many relationship).

SELECT * FROM cities
LEFT JOIN actual_locations ON actual_locations.city = cities.id 
WHERE cities = 'NYC'

Hope it helps!

Answer 2

You can create a query that selects rows that match any of the three possibilities (matching Country, Region and City, matching Country and Region, matching Country). In that query you can use IF statements to assign a value and rank based on that value.

It's simpler to run a query to see if a record matches all three inputs. If it returns no rows, run a query to match two. If that one returns no rows, match only the Country input.