SQL LEFT JOIN help

Question

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

Answer 1

You probably need to move the id_lang = 1 into the LEFT JOIN clause instead of the WHERE clause. Think of it this way... for all of those rows with no translation the LEFT JOIN gives you back NULLs for all of those translation columns. Then in the WHERE clause you are checking to see if that is equal to 1 - which of course evaluates to FALSE.

It would probably be easier if you included your code in the question next time instead of in a link.

Answer 2

Can you try using

LEFT OUTER JOIN

instead of

NATURAL LEFT JOIN

Answer 3

LEFT JOIN episode_trans
    ON episode_trans.number = episode.number
    AND episode_trans.id_lang = 1
WHERE id_serie=1

Answer 4

I tested the following on MySQL 4.1 - it returns your expected output:

   SELECT s.season_number AS season,
          e.number AS episode,
          et.name
     FROM SEASON s
     JOIN EPISODE e ON e.id_season = s.id_season
LEFT JOIN EPISODE_TRANS et ON et.id_episode = e.id_episode
                          AND et.id_lang = 1
    WHERE s.id_serie = 1
 ORDER BY s.season_number, e.number

Generally, when you use ANSI-92 JOIN syntax you need to specify the join criteria in the ON clause. In MySQL, I know that not providing it for INNER JOINs results in a cross join -- a cartesian product.