join tables and search for data in two diff table in mysql

Table 1: full

id name country street
1  ab   xx      xxx
2  bd   xx      xxx
3  dc   xx      xxx

Table 2: old_data

id language area_name  market_name
1  en       xx         xxx
2  cz       xx         xxx

Table 3 : new_data

id language area market
2  fr       xx   xxx
3  cz       xx   xxx

Now, With MySQL, I want to merge tables and find related details. Logic is : If not available in ‘new_data’ then only find it in ‘old_data’

Output :

id name country street language area market
1  ab   xx      xxx    en       xx   xxx
2  bd   xx      xxx    fr       xx   xxx
3  dc   xx      xxx    cz       xx   xxx

Note : No of rows for Table 1 = No of rows for output table.

Answer

You could left join the two tables, and then use coalesce() to pick the relevant values:

select 
    t.*, 
    coalesce(n.language, o.language) language,
    coalesce(n.area, o.area) area,
    coalesce(n.market, o.market) market
from table_full f
left join table_old_data on o.id = f.id
left join table_new_data n on n.id = f.id

This assumes that language, area and market are never null. If that’s not the cas, then you can change the coalesce() expressions to:

select 
    t.*, 
    case when n.id is not null then n.language else o.language end language,
    case when n.id is not null then n.area else o.area end area,
    case when n.id is not null then n.market else o.market end market
from table_full f
left join table_old_data on o.id = f.id
left join table_new_data n on n.id = f.id