SQL query to find result set with english and other translated words-CodePudding

2 tables are as Eng & Tr which contain following data-

Eng table:-                                      Tr table:-

sn   val1                                         sn  val2   trns
--   ---                                          --  ---    ----
 1   one                                          2   zwei    de
 2   two                                          2   dos     es
 3   three                                        3   drei    de
 4   four                                         4   cuatro  es
 5   five                                         4   quatro  it

I want to get result of values having translated as German language (DE) along with which not translated (like 1, 5 as well). So I want output like below -

sn  val1   val2
--  ---    ----
 1  one    
 2  two   zwei
 3  three drei
 5  five

CodePudding user response：

Use a left join:

SELECT e.sn, e.val1, t.val2
FROM Eng e
LEFT JOIN Tr t
    ON t.sn = e.sn AND t.trns = 'de';

Note that from SQL's point of view, both one and four are really the same in that neither has a translation available in German. My answer therefore reports both of them.