My scenario: There are 3 tables for storing tv show information; season, episode and episode_translation.

My data: There are 3 seasons, with 3 episodes each one, but there is only translation for one episode.

My objetive: I want to get a list of all the seasons and episodes for a show. If there is a translation available in a specified language, show it, otherwise show null.

My attempt to get serie 1 information in language 1:

    SELECT
     season_number AS season,number AS episode,name
    FROM
     season NATURAL JOIN episode
     NATURAL LEFT JOIN episode_trans
    WHERE
     id_serie=1 AND
     id_lang=1
    ORDER BY
     season_number,number

result:

+--------+---------+--------------------------------+
| season | episode | name                           |
+--------+---------+--------------------------------+
|      3 |       3 | Episode translated into lang 1 | 
+--------+---------+--------------------------------+

expected result

+-----------------+--------------------------------+
| season | episode| name                           |
+-----------------+--------------------------------+
|      1 |      1 | NULL                           |
|      1 |      2 | NULL                           |
|      1 |      3 | NULL                           |
|      2 |      1 | NULL                           |
|      2 |      2 | NULL                           |
|      2 |      3 | NULL                           |
|      3 |      1 | NULL                           |
|      3 |      2 | NULL                           |
|      3 |      3 | Episode translated into lang 1 |
+--------+--------+--------------------------------+

Full DB dump http://pastebin.com/Y8yXNHrH