Mysql select with Where and default if where condi

问题:

I have tables Product and productDetails. In productDetails I have rows with description of product in many languages (discriminated by lang column). Not every product will have description in every language. How to make a selection that will select the description in the specified language (by Where productDescription.lang = 'en') and that will select a description in a default language if the specified language is not present (descriptions with default language are always present).

I made this:

select *
  from product
  left join productDetails on product.id = productDetails.product_id
 where productDetails.language = 'en';

and I get all details in en language. How to modify this to make details in default language selected if en is not present?

回答1:

Something like that. I don't know your exact schema:

select 
   p.id, 
   if(IS NULL d2.description, d1.description, d2.description ) `description`
 from product p
 join productDetails d1
   on product.id = productDetails.product_id
      and
   productDetails.language = 'default_lang'
 left join productDetails d2
   on product.id = productDetails.product_id 
      and
   productDetails.language = 'en'