logically combine two tables

[Xintare]

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!

[hielo]

Try doing a UNION query and insert a "priority" field depending on which one it is:

NOTE: on the example below, $term represents the value you are searching for

SELECT  locationID, locationName, 1 as priority FROM locations WHERE locationName LIKE '$term%'
      UNION ALL
      SELECT locationID, alternateName, 2 as priority FROM alternateNames WHERE  alternateName LIKE '$term%'
ORDER BY priority ASC"

[smantscheff]

I would prefer a solution without the priority field and with only one occurence of the search term:

select a.locationID, ifnull(alternateName,locationName) as theName,  countryCode
from locations l left join alternatenames a
on l.locationID = a.locationID
having theName like '$term%';

[hielo]

The "locationName" has a higher priority than the alternateName, if the searchterm matches both.

ifnull(alternateName,locationName) as theName does not accommodate that requirement

[smantscheff]

You're right, I overlooked that.
So how about that one:

select a.locationID, 
if(isnull(alternateName),locationName,if(locationName like '$term%',locationName,alternateName)) as theName,  countryCode
from locations l left join alternatenames a
on l.locationID = a.locationID
having  theName like '$term%';