From: | Ragnar <gnari(at)hive(dot)is> |
---|---|
To: | Drew <drewmwilson(at)fastmail(dot)fm> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: How to find missing values across multiple OUTER JOINs |
Date: | 2007-06-06 08:45:46 |
Message-ID: | 1181119547.5953.31.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Drew | 2007-06-06 08:46:14 | Re: How to find missing values across multiple OUTER JOINs |
Previous Message | Richard Huxton | 2007-06-06 08:31:11 | Re: How to find missing values across multiple OUTER JOINs |