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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
