pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to find missing values across multiple OUTER JOINs
4+ messages / 3 participants
[nested] [flat]
* How to find missing values across multiple OUTER JOINs
@ 2007-06-06 06:55 Drew <drewmwilson@fastmail.fm>
2007-06-06 08:31 ` Re: How to find missing values across multiple OUTER JOINs Richard Huxton <dev@archonet.com>
2007-06-06 08:45 ` Re: How to find missing values across multiple OUTER JOINs Ragnar <gnari@hive.is>
0 siblings, 2 replies; 4+ messages in thread
From: Drew @ 2007-06-06 06:55 UTC (permalink / raw)
To: pgsql-sql
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)
RIGHT OUTER JOIN language l on l.language_id = t.language_id;
To test this query, I have a string that only has a translation in
English and used it in this test query.
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
RIGHT OUTER JOIN language l on l.language_id = t.language_id;
This yields promising results:
source_id | translation_pair_id | translation_id | language_id
| name
-----------+---------------------+----------------+-------------
+----------------------
| | | |
Russian
| | | |
Danish
| | | | Dutch
159986 | 1893187 | 1743833 | 4 |
English
| | | |
Finnish
| | | |
French
| | | |
German
| | | |
Italian
| | | |
Japanese
| | | |
Korean
| | | |
Norwegian
| | | |
Simplified Chinese
| | | |
Spanish
| | | |
Swedish
| | | |
Traditional Chinese
| | | |
Portuguese
| | | |
Polish
| | | |
Turkish
| | | | Czech
| | | |
Brazilian Portuguese
(20 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;
I expect 19 rows, but instead get 20 rows, all containing null values.
source_id | translation_pair_id | translation_id | language_id
| name
-----------+---------------------+----------------+-------------
+----------------------
| | | |
Russian
| | | |
Danish
| | | | Dutch
| | | |
English
| | | |
Finnish
| | | |
French
| | | |
German
| | | |
Italian
| | | |
Japanese
| | | |
Korean
| | | |
Norwegian
| | | |
Simplified Chinese
| | | |
Spanish
| | | |
Swedish
| | | |
Traditional Chinese
| | | |
Portuguese
| | | |
Polish
| | | |
Turkish
| | | | Czech
| | | |
Brazilian Portuguese
(20 rows)
I'm guessing I need to group the joins together, to avoid some
associative problem.
Do you see what I'm doing wrong?
Thanks for the help,
Drew
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find missing values across multiple OUTER JOINs
2007-06-06 06:55 How to find missing values across multiple OUTER JOINs Drew <drewmwilson@fastmail.fm>
@ 2007-06-06 08:31 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 4+ messages in thread
From: Richard Huxton @ 2007-06-06 08:31 UTC (permalink / raw)
To: Drew <drewmwilson@fastmail.fm>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find missing values across multiple OUTER JOINs
2007-06-06 06:55 How to find missing values across multiple OUTER JOINs Drew <drewmwilson@fastmail.fm>
@ 2007-06-06 08:45 ` Ragnar <gnari@hive.is>
2007-06-06 08:46 ` Re: How to find missing values across multiple OUTER JOINs Drew <drewmwilson@fastmail.fm>
1 sibling, 1 reply; 4+ messages in thread
From: Ragnar @ 2007-06-06 08:45 UTC (permalink / raw)
To: Drew <drewmwilson@fastmail.fm>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to find missing values across multiple OUTER JOINs
2007-06-06 06:55 How to find missing values across multiple OUTER JOINs Drew <drewmwilson@fastmail.fm>
2007-06-06 08:45 ` Re: How to find missing values across multiple OUTER JOINs Ragnar <gnari@hive.is>
@ 2007-06-06 08:46 ` Drew <drewmwilson@fastmail.fm>
0 siblings, 0 replies; 4+ messages in thread
From: Drew @ 2007-06-06 08:46 UTC (permalink / raw)
To: Ragnar <gnari@hive.is>; Richard Huxton <dev@archonet.com>; +Cc: 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
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-06-06 08:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-06 06:55 How to find missing values across multiple OUTER JOINs Drew <drewmwilson@fastmail.fm>
2007-06-06 08:31 ` Richard Huxton <dev@archonet.com>
2007-06-06 08:45 ` Ragnar <gnari@hive.is>
2007-06-06 08:46 ` Drew <drewmwilson@fastmail.fm>
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