mike@linkify.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@lexus] \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
key | integer |
[test@lexus] \d bar
Table "public.bar"
Column | Type | Modifiers
--------+---------+-----------
key | integer |
value | text |
[test@lexus] select * from foo;
key
-----
1
3
(2 rows)
[test@lexus] select * from bar;
key | value
-----+-------
1 | Mike
2 | Joe
(2 rows)
[test@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@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
