Cc: pgsql-sql@postgresql.org
Message-Id: <84E8CEE0-4F7B-4DF3-979E-65E007DE83B8@fastmail.fm>
From: Drew <drewmwilson@fastmail.fm>
To: Ragnar <gnari@hive.is>,
 Richard Huxton <dev@archonet.com>
In-Reply-To: <1181119547.5953.31.camel@localhost.localdomain>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed; delsp=yes
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Apple Message framework v890.2)
Subject: Re: How to find missing values across multiple OUTER JOINs
Date: Wed, 6 Jun 2007 01:46:14 -0700
References: <4FD9368D-D4CA-44CF-887B-EBB64DCE46BA@fastmail.fm>
	<1181119547.5953.31.camel@localhost.localdomain>

Thanks! That was it.

Drew

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

> On =FEri, 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 =20
>> 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 =3D =
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 =3D 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 =3D =20
> t.translation_id
>     RIGHT OUTER JOIN language l
>         on l.language_id =3Dt.language_id
> WHERE t.translation_id is null;
>
>
> (i did not check the rest of your query)
>
> hope this helps,
> gnari
>
>