StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL LEFT JOIN help
text
Body
copied!<p><strong>My scenario:</strong> There are 3 tables for storing tv show information; season, episode and episode_translation.</p> <p><strong>My data:</strong> There are 3 seasons, with 3 episodes each one, but there is only translation for one episode.</p> <p><strong>My objetive:</strong> 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.</p> <p><strong>My attempt to get serie 1 information in language 1:</strong></p> <pre><code> 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 </code></pre> <p><strong>result:</strong></p> <pre><code>+--------+---------+--------------------------------+ | season | episode | name | +--------+---------+--------------------------------+ | 3 | 3 | Episode translated into lang 1 | +--------+---------+--------------------------------+ </code></pre> <p><strong>expected result</strong></p> <pre><code>+-----------------+--------------------------------+ | 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 | +--------+--------+--------------------------------+ </code></pre> <p><strong>Full DB dump</strong> <a href="http://pastebin.com/Y8yXNHrH" rel="nofollow noreferrer">http://pastebin.com/Y8yXNHrH</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload