mysql - SQL LEFT JOIN help -

My scenario: There are 3 tables to keep TV show information; Season, Episode and Episode_translation.

My data: is 3 seasons, with every 3 episodes, but there is only one episode translated for it.

My Correction: I want to get a list of all the seasons and episodes for a show. If there is a translation available in a particular language, then show it, otherwise show zero.

My attempt to get serie 1 notification in language 1:

  SELECT Season_Number AS Season, Number AS episode, Nationwide Join Episode from Season National Left Join Epistophone WHERE id_serie = 1 AND id_lang = 1 ORDER BY Season_Number, number  

Result:

  + - ------ + --------- + --------------- ----------------- + | Weather | Case | Name | + -------- + --------- + ------------------------------ - + | 3 | 3 | Translated into Episode Long 1 | + -------- + --------- + ------------------------------ - +  

Expected Result

  + ---------------- - + -------------------------------- + | Weather | Case | Name | + ----------------- + ------------------------------- - + | 1 | 1 | Faucet | 1 | 2 | Faucet | 1 | 3 | Faucet | 2 | 1 | Faucet | 2 | 2 | Faucet | 2 | 3 | Faucet | 3 | 1 | Faucet | 3 | 2 | Faucet | 3 | 3 | Translated into Episode Long 1 | + -------- + -------- + ------------------------------- - +  

Full db dump

< P> You probably have to move id_lang = 1 to the LEFT JOIN clause instead of the WHERE clause. Think about it like this ... LEFT JOIN lets you tap back all those translation columns for those rows of no translation. Then in the WHERE section you are checking to see that it is equal to 1 - which definitely evaluates FALSE.

This will probably be easier if you link your code the next time instead of the link.