[Dbix-class] JOIN question

[Jess Robinson]

On Thu, 30 Nov 2006, Bernhard Graf wrote:

> Matt S Trout wrote:
>
>> { -or => [ { 'chunks.id => undef' }, { 'chunks_2.id' => undef }, {
>> 'chunks.id' => \'!= chunks2.id' } ] }
>>
>> i.e.
>>
>> WHERE (chunks.id IS NULL OR chunks_2.id IS NULL OR chunks.id !=
>> chunks2.id)
>>
>> It's not amazingly elegant, which is why the JOIN ON syntax is
>
> No. Does not work.
>
>> generally preferred, but by and large you can move stuff in and ON
>> clause into the WHERE clause or vice versa just fine - a LEFT JOIN is
>> pretty much just a JOIN against all values of that table *plus* a row
>> consisting entirely of NULLs.
>
> Yeah. And I need the NULLs:
> Your query only finds multi-part emails, because only for those
> "chunks.id!=chunks_2.id" is true if this is in the WHERE part.
>
> "chunks.id IS NULL OR chunks_2.id IS NULL" in the WHERE part is useless,
> because they will never be NULL without "chunks.id!=chunks_2.id" in the
> LEFT JOIN.
>
>> This is why I hate it when people tell me something "must be"
>> different to my suggestions without justification - if you don't
>> explain *why* you think that, I can't tell you the bit you're missing
>> :)
>
> I'm still not convinced. ;-P
>

I'm attempting to follow this, and I'm still not convinced.. Can you toss 
out an example with actual data, show what rows your query produces, and 
what Matt's gets? I also think its possible without better JOIN conds..

(That's not to say I don't want them, I do.. but they do need careful 
attention, and many tests ;)

Jess