Re: How to find missing values across multiple OUTER JOINs
От | Drew |
---|---|
Тема | Re: How to find missing values across multiple OUTER JOINs |
Дата | |
Msg-id | 84E8CEE0-4F7B-4DF3-979E-65E007DE83B8@fastmail.fm обсуждение исходный текст |
Ответ на | Re: How to find missing values across multiple OUTER JOINs (Ragnar <gnari@hive.is>) |
Список | pgsql-sql |
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 > >
В списке pgsql-sql по дате отправления: