Implementing a fallback in fetching multilingual data

Question

I'm in the process of translating an existing MySQL table according to this answer. In this case I'm dealing with menu_items, and my table structure now looks as follows:

• menu_items
  • id
  • sort_position
• menu_item_translations
  • menu_item_id
  • language ENUM('nl', 'en')
  • label
  • tooltip

I'm now looking for a way to JOIN these tables in such a way, that I can use one language as a fallback while searching for records of another.

The standard query would be something like this:

SELECT 
    *
FROM
    menu_items mi
INNER JOIN menu_item_translations mit
    ON mit.menu_item_id = mi.id
WHERE
    mit.language = 'en';

But I would like 'nl' to be the fallback in case an English menu item has not been found. I'm not too keen on stored procedures, but I can use them if I have to.

Is there any way to implement a fallback in this query, without too much performance loss or ugliness?

Answer 1

Realised right after posting this question that the answer is fairly simple;

SELECT 
    mi.*,
    COALESCE(mit.label, mit_fb.label) AS label
FROM
    menu_items mi
LEFT JOIN menu_item_translations mit
    ON mit.menu_item_id = mi.id
    AND mit.language = 'en'
LEFT JOIN menu_item_translations mit_fb
    ON mit_fb.menu_item_id = mi.id
    AND mit_fb.language = 'nl';

This does exactly what I want.