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!