Re: How to find missing values across multiple OUTER JOINs

Thanks! That was it.

Drew

On Jun 6, 2007, at 1:45 AM, Ragnar wrote:

> On þri, 2007-06-05 at 23:55 -0700, Drew wrote:
>> I'm having troubles using multiple OUTER JOINs, which I think I want
>> to use to solve my problem.
>>
>> My problem is to find all non-translated string values in our
>> translations database, given the 4 following tables:
>>
>> SOURCE (source_id PRIMARY KEY, language_id, value, ...)
>> TRANSLATION (translation_id PRIMARY KEY, language_id, value, ...)
>> TRANSLATION_PAIR (source_id, translation_id)
>> LANGUAGE(language_id PRIMARY KEY, name)
>>
>> This seems to me the appropriate situation for using OUTER JOINs, but
>> I cannot figure out how to get the null rows without the not-null
>> rows.
>>
>> Here's my best guess at this query:
>> SELECT
>> s.source_id,tp.translation_pair_id,t.translation_id,t.language_id,
>> l.name
>>
>>                                                     FROM source s
>> LEFT OUTER JOIN translation_pair tp USING(source_id)
>> LEFT OUTER JOIN translation t ON tp.translation_id = t.translation_id
>> AND t.translation_id is null
> move this condition out of the ON clause into a WHERE clause
>> )
>> RIGHT OUTER JOIN language l on l.language_id = t.language_id;
>
>
> SELECT s.source_id,
>       tp.translation_pair_id,
>       t.translation_id,
>       t.language_id,
>       l.name
> FROM source s
>     LEFT OUTER JOIN translation_pair tp USING(source_id)
>     LEFT OUTER JOIN translation t ON tp.translation_id =
> t.translation_id
>     RIGHT OUTER JOIN language l
>         on l.language_id =t.language_id
> WHERE t.translation_id is null;
>
>
> (i did not check the rest of your query)
>
> hope this helps,
> gnari
>
>