Retrieve columns from the same table with the same foreign keys but different values

Question

This is a quite delicate question, but I'll give my best to make it clear:

I have a table countrynames with 3 columns: iso, lang, name. The main spelling for these is english, for example:

+-----+------+-----------------------------+
| iso | lang | name                        |
+-----+------+-----------------------------+
| CA  | EN   | Canada                      |
| CC  | EN   | Cocos (Keeling) Islands     |
| CF  | EN   | Central African Republic    |
| CG  | EN   | Congo                       |
| CI  | EN   | Cote d'ivoire (Ivory Coast) |
| CK  | EN   | Cook Islands                |
| CL  | EN   | Chile                       |
| CM  | EN   | Cameroon                    |
| CN  | EN   | China                       |
| CO  | EN   | Colombia                    |
+-----+------+-----------------------------+

iso is the foreign key which references the iso-3166 coding for countries.

I inserted a number of countries with english spelling, but also in other languages ONLY when the spelling is different from english, in order to save some space in the database (for example "Germany" in german is "Deutschland", so the iso for 'DE' appears twice in the table, with 2 different names).

What I need is to select all the countries in some specific language (like german, or spanish), then all countries with english names from the same column. Where there is no corresponding name in the foreign language, then the english name should fill the empty field, so that I get a mixture of english + foreign language. The mixture should be equal to the sum of countries with english spelling, like this (nulls are where the english names should fill the missing spanish names, like Congo, Chile, China, Colombia):

'CA', 'ES', 'Canadá'
'CC', 'ES', 'Islas Cocos'
'CD', 'ES', 'República Democrática del Congo'
'CF', 'ES', 'República Centroafricana'
NULL, NULL, NULL
'CH', 'ES', 'Suiza'
'CI', 'ES', 'Costa del Marfil'
'CK', 'ES', 'Islas Cook'
NULL, NULL, NULL
'CM', 'ES', 'Camerún'
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL

This is what I used so far:

select result2.iso, result2.lang, result2.name from (select result1.* from (select  t1.*
from (
select iso, lang, name from countrynames where lang = 'es') t1
right join
(select iso, lang, name from countrynames where lang = 'en') t2
on t1.iso = t2.iso) result1) result2;

score 0 · Accepted Answer · answered May 21 '12 at 22:57

Your original SELECT can be simplified a little:

SELECT t1.iso, t1.lang, t1.name
  FROM (SELECT * FROM countrynames WHERE lang = 'ES') t1
  RIGHT JOIN (SELECT * FROM countrynames WHERE lang = 'EN') t2
  ON t1.iso = t2.iso;

Then all you need do is coalesce the column results, so you retrieve the t2 values if there are no values in t1:

SELECT COALESCE(t1.iso, t2.iso), COALESCE(t1.lang, t2.lang), COALESCE(t1.name, t2.name)
  FROM (SELECT * FROM countrynames WHERE lang = 'ES') t1
  RIGHT JOIN (SELECT * FROM countrynames WHERE lang = 'EN') t2
  ON t1.iso = t2.iso;

thanks a lot! Solutions are sometimes so easy to find, that we lose the track of things looking for complicated ways to achieve the goal :) — Dragos, May 22 '12 at 00:22

Retrieve columns from the same table with the same foreign keys but different values

1 Answers1