Re: Newbie SELECT ... NOT IN query problem

Here is an example:

This translator 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 can be translated by this specific translator.

Here is some 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"

/ Jacob


On Thu, 12 Apr 2001 10:57:44 +0200, Jacob Nordgren
<jacob_nordgren_at_hotmail.com> wrote:



>

>Hi Steve,

>

>The final result should be a row of TRANSLATED_TEXT.IDs. The row

>should contain all the texts that a specific translator can translate.

>

>The texts that needs to be translated are the missing rows in the

>TRANSLATION table. A specific translator can translate all, some or

>none of these texts depending on which languages he knows (can

>TRANSLATE_FROM and 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)

>

>If the answer is yes on these three question then the

>TRANSLATED_TEXT.ID for that text should be in the result.

>

>I'll try to write a query that may be more specific on what I want:

>

>SELECT TRT.ID

>

>FROM TRANSLATOR T, TRANSLATES_TO TT, TRANSLATES_FROM TF, LANGUAGES L1,

>LANGUAGES L2, TRANSLATION TL1, TRANSLATION TL2, TRANSLATED_TEXT TRT 

>

>WHERE T.ID = 1 AND T.ID = TF.TRANSLATORID AND TF.LANGUAGEID = L1.ID

>AND L1.ID = TL1.LANGUAGE_ID AND TL1.TRANSLATED_TEXT_ID = TRT.ID AND

>T.ID = TT.TRANSLATORID AND TRT.ID NOT IN (<THIS IS WHERE I GET LOST>)

>

>About your solution: I'm using Oracle, however I'm trying to write

>queries that follow the standards as much as possible. I'm not sure

>that MINUS is SQL standard and I'm not sure that can solve my problem

>in this case...

>

>Thank you for your help,

>

>/ Jacob

>

>

>

>On Wed, 11 Apr 2001 16:53:24 GMT, Steve Bell

><swayne.bell_at_sympatico.ca> wrote:

>

>>Hi Jacob,

>>

>>I'm not totally clear on your needs, but is there a way of getting at this

>>with a MINUS query? i.e. subtracting the translations already done from

>>the pending ones?

>>

>>Here's an example of what I'm asking:

>>

>>SQL> SELECT * FROM TRANSLATES_FROM;

>>           1          1

>>           1          2

>>           1          3

>>           2          1

>>

>>4 rows selected.

>>

>>SQL> SELECT * FROM TRANSLATES_TO;

>>           1          1

>>

>>1 row selected.

>>

>>SQL> SELECT * FROM TRANSLATES_FROM

>>  2  MINUS

>>  3  SELECT * FROM TRANSLATES_TO;

>>           1          2

>>           1          3

>>           2          1

>>

>>3 rows selected.

>>

>>I hope this is helpful is some way.

>>

>>Steve

>

>

>>

>>Jacob Nordgren wrote:

>>

>>> Hi everybody,

>>>

>>> I'm having a hard time figuring out this query, but I think it isn't

>>> so hard if anyone with a little experience look at it.

>>>

>>> This is a part of a system used for keeping control of texts and which

>>> text that should be translated.

>>>

>>> What I want to find out is which texts (TRANSLATED_TEXT.ID) that a

>>> specific translator should translate. Texts that the translator should

>>> translate are the texts where there already exists texts that he can

>>> translate from and at the same time there are missing texts in

>>> languages that he can translate to.

>>>

>>> 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"

>>>

>>> I've experimented a lot but I don't think you will have any use of the

>>> queries that I've produced... I think I should use "NOT IN", but I'm

>>> not sure...

>>>

>>> Thank you for your help!!

>>>

>>> / Jacob