Can I use self join to fetch this table

Question

I have some data in the following format:

Countries:

lang | code | name
==========================
EN     1001   Italy
IT     1001   Italia
RO     1001   Italia
AL     1001   Itali
TR     1001   Italya
--------------------------
EN     1002   Greece
RO     1002   Grecia
AL     1002   Greqi
TR     1002   Yunanistan
--------------------------
EN     1003   Romania
AL     1003   Rumani
TR     1003   Romanya
--------------------------

I want to select all names, but with one more column, in which I list the name of that country (having same code) in a specific language (say EN).

I want to print this table:

lang | code | name        | name_EN
===================================
EN     1001   Italy         Italy
IT     1001   Italia        Italy
RO     1001   Italia        Italy
AL     1001   Itali         Italy
TR     1001   Italya        Italy
-----------------------------------
EN     1002   Greece        Greece
RO     1002   Grecia        Greece
AL     1002   Greqi         Greece
TR     1002   Yunanistan    Greece
-----------------------------------
EN     1003   Romania       Romania
AL     1003   Rumani        Romania
TR     1003   Romanya       Romania
-----------------------------------

I tried using self join, but did not make it.

SELECT t1.lang AS lang
    , t1.code  AS code
    , t1.name  AS name
    , t2.name  AS name_EN
FROM countries t1
INNER JOIN countries t2 
    ON t2.name = t1.name
    AND t2.lang = 'EN'

From this query I get only the name in lang EN; 1 name per country.

lang | code | name        | name_EN
===================================
 EN    1001   Italy         Italy
 EN    1002   Greece        Greece
 EN    1003   Romania       Romania

If I remove t2.lang = 'EN' condition, the new column is identical to the existing name column.

1. Can I actually get this kind of table using self join? If yes, how?

2. If this cannot be done with self join, is it worth it (performance-wise) trying to do it in MySql, or am I better of doing it programmatically?

You can test it here. https://paiza.io/projects/e/oahtrlObx_ctyHBZFnzDvA

Answer 1

This query will give you the results you want. It uses a self join, but only to the values in the table which have lang=EN and it joins on code so we can fetch the English name regardless of current language:

SELECT t1.lang, t1.code, t1.name,
       t2.name AS name_EN
FROM countries t1
JOIN countries t2 ON t2.lang = 'EN' AND t1.code = t2.code

Output:

lang    code    name        name_EN
EN      1001    Italy       Italy
IT      1001    Italia      Italy
RO      1001    Italia      Italy
AL      1001    Itali       Italy
TR      1001    Italya      Italy
EN      1002    Greece      Greece
RO      1002    Grecia      Greece
AL      1002    Greqi       Greece
TR      1002    Yunanistan  Greece
EN      1003    Romania     Romania
AL      1003    Rumani      Romania
TR      1003    Romanya     Romania

Demo on dbfiddle