mysql database join based on a mapping

Question

Hi, guys,

An abstraction of the problem is like this:

I have one table having a column called 'country'. the value stored are name of the country, e.g. US, UK..

I have another table having a column called 'country_code'. the value stored are numerical representations of the country, e.g. 12, 17...

how can I perform a join operation (e.g. inner join) based on these 2 tables? the difficulty is that the country and country_code has a one-to-one mapping but not directly equal to each other.

Answer 1

You could create a Mapping table containging the country and the country_code.

I assume you cannot change the table containing the country_code to use the string representation from country, or add an int column to your countries table?

Something like

country_mappings

• country varchar column
• country_code int column
• PRIMARY KEY country, country_mapping

'

SELECT  *
FROM    countries c INNER JOIN
        country_mappings cm ON c.country = cm.country inner join
        your_other_table yot ON cm.country_code = yot.country_code