Left join not working

[jR³]

Hello,
I need to do something like this:
<snippet from mysql docs: http://dev.mysql.com/doc/refman/5.0/en/join.html>

SELECT table1.* FROM table1
  LEFT JOIN table2 ON table1.id=table2.id
  WHERE table2.id IS NULL;

This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 with no corresponding row in table2). This assumes that table2.id is declared NOT NULL.

</snippet>

I'm working on a multi- language site,
and I need to select all rows from one language that are not present in another language..
lang_id ( is the language id f.e. en/ fr / nl)
language_key( is the key of the translated part )
languages

SELECT l.language_id,l.language_key FROM languages l
left JOIN languages l2 on l.language_key=l2.language_key 
where l2.language_key IS NULL AND l.language_id='en'
AND l2.language_id='nl'

So in this query I select all the translated parts that are present in the English language but need to be translated to Dutch.

Thanks in advance!

jr3

[r937]

This assumes that table2.id is declared NOT NULL.

actually, it doesn't matter if it is or not



as for your query, it follows the same pattern, but one of the conditions must go into the ON clause:

select l.language_id
     , l.language_key 
  from languages l
left 
  join languages l2 
    on l2.language_key = l.language_key 
   and l2.language_id = 'nl'
 where l.language_id = 'en'
   and l2.language_key is null