Re: How to find missing values across multiple OUTER JOINs

Поиск
Список
Период
Сортировка
От Richard Huxton
Тема Re: How to find missing values across multiple OUTER JOINs
Дата
Msg-id 466670CF.8020303@archonet.com
обсуждение исходный текст
Ответ на How to find missing values across multiple OUTER JOINs  (Drew <drewmwilson@fastmail.fm>)
Список pgsql-sql
Drew wrote:

> 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.

> However, when I try to exclude the one not-null row, doing this:
>   SELECT 
> s.source_id,tp.translation_pair_id,t.translation_id,t.language_id, l.name
>     FROM (select * FROM source s WHERE source_id = 159986) AS 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
>         RIGHT OUTER JOIN language l on l.language_id = t.language_id;

Ah, you need to put the "IS NULL" outside the join.

SELECT
...
WHERE  s.source_id IS NULL OR tp.translation_pair_id IS NULL OR ...
--   Richard Huxton  Archonet Ltd


В списке pgsql-sql по дате отправления:

Предыдущее
От: Ragnar
Дата:
Сообщение: Re: How to find missing values across multiple OUTER JOINs
Следующее
От: Drew
Дата:
Сообщение: Re: How to find missing values across multiple OUTER JOINs