Take the 2-minute tour ×

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: AlternateNames:

altNameID |   locationID    |  alternateName    |
1         |      2922239    |     Berlino       | 
2         |      2922239    |     Berlina       |
3         |      2922239    |     capital       |
4         |      291074     |     Parisa        |
5         |      291074     |     Pariso        | 
6         |      295522     |     Orlandola     |
7         |      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!

share|improve this question

4 Answers 4

up vote 3 down vote accepted
   (IF (name LIKE 'Ber%', name, alternateName)) as name, 
   Locations l LEFT JOIN AlternateNames a ON (l.locationID = a.locationID)
  name LIKE 'Ber%'
  alternateName LIKE 'Ber%'

Obviously, use variable substitution (:searchname) in place of 'Ber%'.

Add LIMIT 1 to the end of the query if you only want 1 row returned.

Edited based on comment about preference for alternateName. Made LEFT join per Schultz999 suggestion

share|improve this answer
Thanks, but if I'm looking for "Berlino%" I would still get "Berlin" as locationName –  W.Gerick Jan 16 '11 at 2:47
@W.Gerick: James is looking for 'Ber'. If you are looking for 'Berlino', the query will return 'Berlino'. And it was made clear when he said you needed to replace the search string with a variable. –  Schultz9999 Jan 16 '11 at 3:02
@James: the only correction I'd made is LEFT JOIN the tables because there might be no alternatives to the name. –  Schultz9999 Jan 16 '11 at 3:03
@Schultz9999 Thanks, changed –  James Jan 16 '11 at 3:17
@James: btw, it still need some correction in regard of priority. Consider the case in the question: Berlin vs Berlino. The search string is Ber so the main name, Berlin, must be returned. However in your case (IF (alternateName LIKE 'Ber%', alternateName, name)) will result alternative name instead. –  Schultz9999 Jan 16 '11 at 3:23

something like this should do this.

select a.locationID, coalesce(b.alternateName,a.locationName), a.countrycode FROM table1 a LEFT JOIN table2 b ON a.locationId=B.locationID where a.locationName=?
share|improve this answer
Thanks, but with this query there is no way to search for an alternateName like "Berlino" –  W.Gerick Jan 16 '11 at 2:55

Try this:

SELECT a.locationID, :locationName, a.countryCode // :locationName being the city name parameter FROM cities a, locations b WHERE a.locationID = b.locationID AND (a.locationName = :locationName OR b.alternateName = :locationName )

SELECT a.locationID, a.locationName, a.countryCode 
  FROM cities a
 WHERE a.locationName = :locationName
SELECT a.locationID, b.alternateName locationName, a.countryCode
  FROM cities a, locations b
 WHERE a.locationID = b.locationID
   AND b.alternateName =  :locationName 
share|improve this answer
Thanks, but if I'm looking for "Berlin" I would get two hits. Also, searching for "Berlino" would return "Berlin". –  W.Gerick Jan 16 '11 at 2:52
Updated the query. Pls check –  Chandu Jan 16 '11 at 3:01
As long as there are no alternatives matching the orignal, it should work just fine. But you need to use LIKE. –  Schultz9999 Jan 16 '11 at 3:07

This query joins the two tables via a union query, ranks them and takes the top result from the union.

Try this:

    , `name`
    , countryCode
                , `name`
                , countryCode
                , 1 AS priority
            locationName LIKE 'Ber%'
                , a.alternateName AS `name`
                , l.countryCode
                , 2 as priority
                AlternateNames a
                INNER JOIN Locations l
                    ON a.locationID = l.locationID
                a.alternateName LIKE 'Ber%'
    ) u
share|improve this answer

Your Answer


