Sqlite3 left join and where cause with additional negation

Question

I have two tables:

sqlite> select * from gender_age_brand_train limit 10;
index       Unnamed: 0  device_id             gender      age         group       phone_brand
----------  ----------  --------------------  ----------  ----------  ----------  -----------
1           0           -8076087639492063270  M           35          M32-38      小米         
2           1           -2897161552818060146  M           35          M32-38      小米         
3           2           -8260683887967679142  M           35          M32-38      小米         
4           3           -4938849341048082022  M           30          M29-31      小米         
5           4           245133531816851882    M           30          M29-31      小米         
6           5           -1297074871525174196  F           24          F24-26      OPPO       
7           6           236877999787307864    M           36          M32-38      酷派         
8           7           -8098239495777311881  M           38          M32-38      小米         
9           8           176515041953473526    M           33          M32-38      vivo       
10          9           1596610250680140042   F           36          F33-42      三星       

The second table:

sqlite> select * from phone_brand_latin_new limit 10;
index       chinese     latin_brands
----------  ----------  ------------
1           三星          samsung     
2           天语          Ktouch      
3           海信          hisense     
4           联想          lenovo      
5           欧比          obi         
6           爱派尔         ipair       
7           努比亚         nubia       
8           优米          youmi       
9           朵唯          dowe        
10          黑米          heymi   

I have write the following Query:

sqlite> SELECT * FROM gender_age_brand_trai LEFT JOIN phone_brand_latin_new WHERE phone_brand = chinese limit 20;

With the following Output:

sqlite> SELECT * FROM gender_age_brand_trai LEFT JOIN phone_brand_latin_new WHERE phone_brand = chinese limit 10;
index       Unnamed: 0  device_id             gender      age         group       phone_brand  index       chinese     latin_brands
----------  ----------  --------------------  ----------  ----------  ----------  -----------  ----------  ----------  ------------
1           0           -8076087639492063270  M           35          M32-38      小米           59          小米          xiaomi      
2           1           -2897161552818060146  M           35          M32-38      小米           59          小米          xiaomi      
3           2           -8260683887967679142  M           35          M32-38      小米           59          小米          xiaomi      
4           3           -4938849341048082022  M           30          M29-31      小米           59          小米          xiaomi      
5           4           245133531816851882    M           30          M29-31      小米           59          小米          xiaomi      
7           6           236877999787307864    M           36          M32-38      酷派           57          酷派          coolpad     
8           7           -8098239495777311881  M           38          M32-38      小米           59          小米          xiaomi      
10          9           1596610250680140042   F           36          F33-42      三星           1           三星          samsung     
11          10          9032155484127182494   M           31          M29-31      华为           55          华为          huawei      
12          11          7477216237379271436   F           37          F33-42      华为           55          华为          huawei  

This query fades out the non-chinese brands like OPPO. I want to have in a one column all of non-chinese brands. The Information for only non-chinese brands are in gender_age_brand_train.phone_brands and phone_brand_latin_new.latin_brands. How can I do this in this case?

Answer 1

My Solution in this case is based of the approach from @Indent

sqlite> SELECT
   ...>     latin_brands,
   ...>     case
   ...>     WHEN chinese is not null
   ...>     then phone_brand
   ...>     else phone_brand
   ...>     end as phone_brand_chinese_match,
   ...>     case 
   ...>     WHEN chinese is not null
   ...>     then latin_brands
   ...>     else phone_brand
   ...>     end as only_latin_brands
   ...> FROM
   ...>     gender_age_brand_trai
   ...> LEFT JOIN phone_brand_latin_new on
   ...>      phone_brand = chinese limit 10;

latin_brands  phone_brand_chinese_match  only_latin_brands
------------  -------------------------  -----------------
xiaomi        小米                         xiaomi           
xiaomi        小米                         xiaomi           
xiaomi        小米                         xiaomi           
xiaomi        小米                         xiaomi           
xiaomi        小米                         xiaomi           
              OPPO                         OPPO             
coolpad       酷派                         coolpad          
xiaomi        小米                         xiaomi           
              vivo                         vivo             
samsung       三星                         samsung          
sqlite> 

Answer 2

Use left join with on clause and case like this :

SELECT
    *,
    case
    when chinese is not null
    then phone_brand
    else null
    end as phone_brand_chinese_match,
    case
    when chinese is null
    then phone_brand
    else null
    end as phone_brand_chinese_only
FROM
    gender_age_brand_trai
    LEFT JOIN phone_brand_latin_new on
         phone_brand = chinese