Select entries in "joined" table matching specific data and/or non-existing data

I created two tables 开发者_如何学Pythonto manage a multi language article system :

• table1 is a table which is used as an index of all the articles registered

  table1 = (ART_ID, ART_AUTHOR, ART_DATE)

• table2 is a table which is used to store all languages versions of an article

  table2 = (LOC_ID, LOC_TITLE, LOC_TEXT, LOC_LANG, ART_ID)

My goal is the following:

If I select English as language;

1. I need to see the articles with an existing English localisation so I can edit them,

   [if there is a localisation matching the selected language then it returns this one as table2 data's].

2. I also need to see the articles without any localisation yet (but the article index exists in the table1 already) so I can write the English version,

   [if there is no localisation at all for the article, then it returns null data's as table2 data's].

3. And finally, I need to see the articles that already have a localisation in another language so I can write the English version.

   [if there is no localisation matching the selected language but there is another localisation, then it returns null data's as table2 data's.]

but I was unable to write the good query until now...

If I use:

SELECT table1.ART_AUTHOR, table1.ART_DATE, table2.LOC_TITLE, table2.LOC_TEXT
       FROM table1
       LEFT JOIN table2 ON ( table1.ART_ID = table2.ART_ID )

it returns all the article in all languages, but I need null data's if the selected language is not yet localized. So it's not good.

If I use:

SELECT table1.ART_AUTHOR, table1.ART_DATE, table2.LOC_TITLE, table2.LOC_TEXT
       FROM table1
       LEFT JOIN table2 ON ( table1.ART_ID = table2.ART_ID )
       WHERE table2.LOC_LANG = 'en'

it returns all the article written in English, but not those without any localisation neither those with localisation in another language. So it's not good.

If I use:

SELECT table1.ART_AUTHOR, table1.ART_DATE, table2.LOC_TITLE, table2.LOC_TEXT
       FROM table1
       LEFT JOIN table2 ON ( table1.ART_ID = table2.ART_ID )
       WHERE table2.LOC_LANG = 'en' OR table2.LOC_LANG IS NULL

it returns all the article written in English and all article without localisation at all, but not those with localisation in another language. So it's not good.

I tried with some sub-queries and exists or not exists but nothing was reaching the goal.

Does anybody knows something I could use in order to get this working ? Is this is even possible ?

Thank you.

Try to put the condition in the left join:

SELECT table1.ART_AUTHOR, table1.ART_DATE, table2.LOC_TITLE, table2.LOC_TEXT
 FROM table1
 LEFT JOIN table2 ON table1.ART_ID = table2.ART_ID
                 and table2.LOC_LANG = 'en'