From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | mike(at)linkify(dot)com |
Cc: | sszabo(at)megazone(dot)bigpanda(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: correlated delete with 'in' and 'left outer join' |
Date: | 2004-02-27 07:07:22 |
Message-ID: | 403EECAA.1010901@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
mike(at)linkify(dot)com wrote:
> The subquery will always return a row from LogEvent, but that row's itemID
> will be null if the itemID doesn't match a row from Item.
> That's why the subquery has the "and i.ItemID is null".
You lost me.
[test(at)lexus] \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
key | integer |
[test(at)lexus] \d bar
Table "public.bar"
Column | Type | Modifiers
--------+---------+-----------
key | integer |
value | text |
[test(at)lexus] select * from foo;
key
-----
1
3
(2 rows)
[test(at)lexus] select * from bar;
key | value
-----+-------
1 | Mike
2 | Joe
(2 rows)
[test(at)lexus] select f.key from foo f left outer join bar b on f.key
= b.key and b.key is null;
key
-----
1
3
(2 rows)
To do what I think you believe to be happening w.r.t. outer joins,
you'd have to have a subquery like:
[test(at)lexus] select a.fookey
test-# FROM
test-# (SELECT foo.key AS fookey, bar.key as barkey FROM foo LEFT
OUTER JOIN bar ON foo.key = bar.key) AS a
test-# WHERE a.barkey IS NULL;
fookey
--------
3
(1 row)
Nevertheless, Stephan's solution matches your description of the
problem and excutes the logical equivalent of the above much more
rapidly...
Mike Mascari
From | Date | Subject | |
---|---|---|---|
Next Message | Shachar Shemesh | 2004-02-27 08:59:54 | Re: Windows Library for libpq |
Previous Message | mike | 2004-02-27 06:50:57 | Re: correlated delete with 'in' and 'left outer join' |