Good day,

I've got a MySQL table explanations, which holds fields for example: lang_id, label, short_description, lengthy_description and it is_default.

During my application, product explanations are fetched in the database based on the current language. Everything works fine for the time being, however Let me give a default explanations for every product to ensure that whevener an account within the preferred language is not found, the default description is going to be fetched rather.

At this time, my demands seem like this:

 SELECT 
    description.id AS record_id
    description.label,
    description.short_description,
    description.long_description    		
 FROM
    products,
    description,
    languages
 WHERE
    products.id = '.$someProductID.' AND
    products.id = description.product_id AND
    languages.id = description.lang_id AND
    languages.code = "'.$someLang.'"

Does anybody possess a solution for fetching the default description of the product once the preferred translation does not exist ?

I figured of adding some IFNULL claims to my request, something similar to this:

IFNULL(description.label, (SELECT label FROM description WHERE product_id = '.$someProductID.' AND is_default = 1) ) AS label

But I am not so acquainted with such complex queries and that i could not have great results.

I am available to suggestions )

Thanks !

That one:

SELECT  p.*, COALESCE (dn.name, den.name) AS cname
FROM    products p
LEFT JOIN
        description dn
ON      dn.product_id = p.id
        AND dn.language =
        (
        SELECT  id
        FROM    language
        WHERE   code = 'your_language'
        )
LEFT JOIN
        description den
ON      den.product_id = p.id
        AND den.is_default
        )
WHERE   p.id = @my_product

, or that one:

SELECT  p.*,
        COALESCE (dn.name,
        (
        SELECT  den.name
        FROM    description den
        WHERE   den.product_id = p.id
                AND den.is_default
        )
        ) AS cname
FROM    products p
LEFT JOIN
        description dn
ON      dn.product_id = p.id
        AND dn.language =
        (
        SELECT  id
        FROM    language
        WHERE   code = 'your_language'
        )
WHERE   p.id = @my_product

In most databases but MySQL, the first is much more efficient if you have couple of translations for the language, the 2nd the first is more effective if you have plenty of translations.

In MySQL, the 2nd query (with COALESCE) is definitely more effective.

Check this out number of articles during my blog about this problem for performance particulars:

, and navigate further for other Oracle's

You can join the description table again within different alias (e.g., defaults) where you are only tugging is a result of the default language. Unsure if that is much more of a performance drag than your IFNULL idea.