I have two tables. The first one holds information about cities:
locID | locationID | locationName | countryCode | 1 | 2922239 | Berlin | de | 2 | 291074 | Paris | fr | 3 | 295522 | Orlando | us | 3 | 292345 | Tokyo | jp |
There is a second table, which holds alternative names for locations. There might be NO alternative name for a location in the Locations table:
altNameID | locationID | alternateName | 1 | 2922239 | Berlino | 2 | 2922239 | Berlina | 3 | 291074 | Parisa | 4 | 291074 | Pariso | 5 | 295522 | Orlandola | 6 | 295522 | Orlandolo |
What I would like to get is the locationID, name and the countryCode of a location for a location name search like "Berlin", or "Ber":
| locationID | name | countryCode | | 2922239 | Berlin | de |
However, if the user searches for "Berlino", I would like to get the alternateName back:
| locationID | name | countryCode | | 2922239 | Berlino | de |
The "locationName" has a higher priority than the alternateName, if the searchterm matches both.
I can't figure out how to build a query to do that. Since the name can come from one of the two tables, it seems quite difficult to me.
Any help is really appreciated!