Hi there,
I have two tables. The first one holds information about cities:
Locations:
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:
AlternateNames:
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!