[Dbix-class] Left join with an extra condition

[Peter Rabbitson]

On Sat, Oct 24, 2009 at 05:18:12PM -0700, Darren Duncan wrote:
> Bill Moseley wrote:
>> Looking for how to add an addition condition on a join.  Here's an  
>> example of the query with the extra condition in CAPS
>>
>> select u.* <http://p.id/>, count(o.id <http://o.id>)
>> from user u
>> left outer join job j on   j.user = u.id <http://u.id> AND J.JOB_TYPE = 6
>> where u.location  = ?
>> group by u.*
>>
>> So, wondering how to represent that along with the extra join condition.
>
> What you are adding doesn't look like a normal join condition, as it 
> isn't comparing values in the 2 tables being joined.
>
> On the other hand, any straight equality test certainly is expressible as 
> a join, if you consider the "6" to be a single-column,single-row table, 
> and then you are joining 3 tables.
>
> Is the syntax you propose supposed to be a shorthand for that?
>
> What DBMSs would support this syntax, as I've never seen it before?

Any database that I have thrown it to so far has supported it (namely
MySQL, Pg, MSSQL, SQLite). The way rob explains it is rather limited - 
this is not simply a WHERE condition. Consider:

Artists
-------
Bob
John
Mike


CDs
ID  Artist  Year
-------------------
1   Bob     2000
2   Bob     2001
3   Mike    2001
4   Mike    2002
5   Mike    2003


We want a list of all artists and all their cds - easy:

SELECT * FROM artist a LEFT JOIN cd c ON a.name = c.artist


Now we want a list of all artists and to see if they have
released a CD in the year 2000. The catch is that if there is
no year 2000 cd, we still want to see the artist name. This is
when you need the extra join condition, as WHERE will not cut it

SELECT * FROM artist a LEFT JOIN cd c ON a.name = c.artist AND c.year = 2000

The above will return you either a row with an artist and a cd
released in the year 2000, OR will return you the artist row and
NULLs where the CD would have been.

Cheers