使用可选参数/排名构造SQL查询

Question

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!