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.