difficult query: Join Parameter, self join, and contradicting conditions

Question

I have a Database with a table named translations.(amazing...)

In this table are 4 rows: id, textidea, translation and language.

id is a regular id as every primary key

textidea is the id of the idea behind the word, like an image: when i mean 'tree' the idea can be translated in other languages

translation is the translation of the textidea

and language is the language in of the translation

The short function to this table is: textidea + language = translation

Not every textidea has a translation in every Language: I have 10 Languages and 1100 Textideas, but only 7000 Translations

My job is to fetch every single textidea and the english translation where the target language does not yet have a translation from the database

ill pack it in a txt file and can massively translate the words from english to other languages

my sql query ist quite working: it gets no result(empty result):

SELECT t.textidea,t.translation 

FROM translation AS t  
LEFT JOIN translation AS w             //table joins itself

ON t.textidea = w.textidea 
WHERE (t.language = 'en-US' 
   AND w.language = 'de-DE'             //de-DE is German and is as example 
   AND w.translation IS NULL            //target language here
   AND t.translation IS NOT NULL)

what can i do in my sql query better? if possible you can do a dql query, because im using doctrine too

Thanks

EDIT: if i'm doing a left join, with less entrys than the main table:

---------------------
|entry 1|added entry|
---------------------
|entry 2|null       |
---------------------

i have an entry with null: I want to search that entry

Problem: i want it a special language ~ where de.language = 'de-DE'

but this excludes null

-> can i join with a parameter? so i can put "where de.language ='de-DE'" there and search later for null?

Answer 1

I just found out:

after 'JOIN' follows 'ON' and in this we can specify the join condition, but also things we would put in 'WHERE'

this makes it possible to have tailored tables, which can be tailored on in where

sooo :

old query:

SELECT t.textidea,t.translation 

FROM translation AS t  
LEFT JOIN translation AS w             

ON t.textidea = w.textidea 
WHERE (t.language = 'en-US' 
   AND w.language = 'de-DE'         
   AND w.translation IS NULL            
   AND t.translation IS NOT NULL)

change:

ON t.textidea = w.textidea AND w.language = 'de-DE'

new working query:

SELECT t.textidea,t.translation 

FROM translation AS t  
LEFT JOIN translation AS w             

ON t.textidea = w.textidea AND w.language = 'de-DE'
WHERE (t.language = 'en-US' 
   AND w.translation IS NULL            
   AND t.translation IS NOT NULL)