How do we want CC list or commenter "does not match" to work??

[David Miller]

On 2/6/2004 11:07 PM -0800, slouhaecwz1 at jetable.net wrote:

> Joel Peshkin <bugreport at peshkin.net> wrote:
>
>> LEFT JOIN cc ON cc.bug_id = bug.id AND cc.who = davesuserid
>> WHERE cc.who IS NULL
>
> Won't this just return 0 results?  cc.who can't be both davesuserid
> and NULL at the same time.

It's not.  That should work (and it's a pretty smart idea, Joel! :)

SELECT bug_id
FROM bugs
     LEFT JOIN cc ON cc.bug_id = bugs.bug_id AND cc.who = davesuserid
WHERE cc.who IS NULL

The entire phrase "cc.bug_id = bugs.bug_id AND cc.who = davesuserid" is
part of the join condition, so this will only join the CC table on rows
where 'davesuserid' is in the CC list.  However, it's using LEFT JOIN,
which means it will join the CC table whether it has a matching row or not,
and that position in the results will be NULL if there was no matching row.

After the join is done, then we look at the WHERE part, which says where
"cc.who IS NULL", which now matches any rows in the resulting table which
did not have davesuserid in them.
-- 
Dave Miller      Project Leader, Bugzilla Bug Tracking System
http://www.justdave.net/             http://www.bugzilla.org/