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.
[EMAIL PROTECTED] \d foo
Table "public.foo"
Column | Type | Modifiers
--------+---------+-----------
key | integer |[EMAIL PROTECTED] \d bar
Table "public.bar"
Column | Type | Modifiers
--------+---------+-----------
key | integer |
value | text |[EMAIL PROTECTED] select * from foo; key ----- 1 3 (2 rows)
[EMAIL PROTECTED] select * from bar; key | value -----+------- 1 | Mike 2 | Joe (2 rows)
[EMAIL PROTECTED] 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:
[EMAIL PROTECTED] 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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
