title: mySQL - query to combine two tables
body: <p>I have two tables. The first one holds information about cities:</p>
  
  <p>Locations:</p>
  
  <pre><code>locID     |   locationID    |  locationName    |  countryCode    |
  1         |      2922239    |     Berlin       |      de         |
  2         |      291074     |     Paris        |      fr         |
  3         |      295522     |    Orlando       |      us         |
  3         |      292345     |    Tokyo         |      jp         |
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>altNameID |   locationID    |  alternateName    |
  1         |      2922239    |     Berlino       | 
  2         |      2922239    |     Berlina       |
  3         |      2922239    |     capital       |
  4         |      291074     |     Parisa        |
  5         |      291074     |     Pariso        | 
  6         |      295522     |     Orlandola     |
  7         |      295522     |     Orlandolo     |
  </code></pre>
  
  <p>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":</p>
  
  <pre><code>|   locationID   |   name       |  countryCode    |
  |   2922239      |   Berlin     |        de       | 
  </code></pre>
  
  <p>However, if the user searches for "Berlino", I would like to get the alternateName back:</p>
  
  <pre><code>|   locationID   |   name       |  countryCode    |
  |   2922239      |   Berlino    |        de       | 
  </code></pre>
  
  <p>The "locationName" has a higher priority than the alternateName, if the searchterm matches both.</p>
  
  <p>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.</p>
  
  <p>Any help is really appreciated!</p>