Select query question

Hi everybody,

I'm building a system that should be used for keeping control of texts in multiple languages and translators that should translate texts that not yet are translated.

I'm almost done. All that's missing is a query that I'm having a hard time figuring out allthough I think that some with a little more exprience might figure it out in a blink (or at least point me in the right direction).

What I want to find out are which texts that can be translated by a specific translator.

If I was only to find out all texts that needs to be translated that would be all the missing rows in the TRANSLATION table (look below). There should be a translation for every text in every language.

When finding out which texts that needs to be translated by a specific translator you also have to think about the languages that he can translates from and that he can translate to.

To find out if a specific text can be translated by the specific translator you have to find out

1. Are there any missing translations for the text
2. Are any of the missing translations in a language that the translator can translate to
3. Is there at least one translation of the text that is finished that the translator can understand (can translate from)

(remember that there should always be a relation between the TRANSLATED_TEXT and LANGUAGE table. All texts should be translated!)

If the answer is yes on these three question then the TRANSLATED_TEXT.ID for that text should be in the result. The final result should be a row of these IDs.

These are the tables:

TRANSLATOR
ID NUMBER (PK) TRANSLATES_FROM
TRANSLATORID NUMBER (PK)
LANGUAGEID NUMBER (PK) TRANSLATES_TO
TRANSLATORID NUMBER (PK)
LANGUAGEID NUMBER (PK) LANGUAGES
ID NUMBER (PK)
NAME VARCHAR2 (2) -- example "en"

TRANSLATION
TRANSLATED_TEXT_ID NUMBER (PK)
LANGUAGE_ID NUMBER (PK)
TEXT VARCHAR2 (4000) -- example "Can you help me?" if language is "en"

TRANSLATED_TEXT
ID NUMBER (PK)
NAME VARCHAR2 (200) -- this is an internal name, example "can_you_help"

Here is an example:

Translator 1 is probably from Sweden since he understands both english and swedish. However, only because he understands english does not meen that he writes english good. Therefore he the only language he can translate to is swedish.

Since there is a text that in english and the swedish translation is missing, the result of the query should be that the TRANSLATED_TEXT with ID = 1 should be translated by this specific translator.

The resulting row should therefore be:

RESULT

Here is the data:

TABLE: TRANSLATOR
ID

--
1

TABLE: TRANSLATES_FROM
TRANSLATORID LANGUAGEID
-----------------------
1 1
1 2

TABLE: TRANSLATES_TO
--------------------
TRANSLATORID LANGUAGEID
1 1

TABLE: LANGUAGES
----------------
ID NAME
1 "sv" -- swedish
2 "en" -- english

TABLE: TRANSLATION
------------------
TRANSLATED_TEXT_ID, LANGUAGE_ID, TEXT
1, 2, "Can you help me?"

TABLE: TRANSLATED_TEXT
----------------------
ID, NAME
1, "can_you_help_me"

Thank you for you time!!

/ Jacob