CODEWITHSUNDEEP
mysql
Márius Rak
Márius Rak

Reputation: 1472

MySQL Select at least one language variant

i have following table structure:

table data

And i need to select every item from master, and language variant by lang, however, when reqiured lang is not defined, then select another one variant. So e.g. i want all de mutations, but for master ID 3 and 4 it's not defined, so it'll select the en variants. So how to achieve this, please?

Upvotes: 0

Views: 38

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39487

You can do a double join and use COALESCE to get value for en when de values are absent:

select m.id,
    m.name,
    coalesce(vd.id, ve.id) v_id,
    coalesce(vd.Lang, ve.Lang) Lang,
    coalesce(vd.master_id, ve.master_id) master_id,
    coalesce(vd.Variant_Name, ve.Variant_Name) Variant_Name
from Master m
left join Language_Variant vd on m.id = vd.master_id
    and vd.Lang = 'de'
left join Language_Variant ve on m.id = ve.master_id
    and ve.Lang = 'en'

Demo

Upvotes: 1

Related Questions