From: Richard Huxton <dev@archonet.com>
To: Drew <drewmwilson@fastmail.fm>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to find missing values across multiple OUTER JOINs
Date: Wed, 06 Jun 2007 09:31:11 +0100
Message-ID: <466670CF.8020303@archonet.com> (raw)
In-Reply-To: <4FD9368D-D4CA-44CF-887B-EBB64DCE46BA@fastmail.fm>
References: <4FD9368D-D4CA-44CF-887B-EBB64DCE46BA@fastmail.fm>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, drewmwilson@fastmail.fm
Subject: Re: How to find missing values across multiple OUTER JOINs
In-Reply-To: <466670CF.8020303@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox