Subject: Re: How to find missing values across multiple OUTER JOINs
From: Ragnar <gnari@hive.is>
To: Drew <drewmwilson@fastmail.fm>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <4FD9368D-D4CA-44CF-887B-EBB64DCE46BA@fastmail.fm>
References: <4FD9368D-D4CA-44CF-887B-EBB64DCE46BA@fastmail.fm>
Content-Type: text/plain; charset=ISO-8859-1
Date: Wed, 06 Jun 2007 08:45:46 +0000
Message-Id: <1181119547.5953.31.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

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