Assume i have the following mysql tables:

create table product (
  product_id int(11) NOT NULL auto_increment,
  product_no varchar(20) NOT NULL,
  primary key (product_id)
)

create table language (
  language_id int(11) NOT NULL auto_increment,
  code char(2) NOT NULL,
  primary key (language_id)
)

create table product_description (
  product_id int(11) NOT NULL,
  language_id int(11) NOT NULL,
  description varchar(100) NOT NULL,
  primary key (product_id, language_id)
)

now i want to query all products and their english description. If a product doesn't have an english description, it should be returned anyway. This is the desired query:

select p.*, pd.description 
  from product as p 
  left join product_description as pd 
  join language as l 
    on pd.language_id = l.language_id and l.code = 'en' 
    on p.product_id = pd.product_id

at the moment there's only the following workaround:

$select = $db->select();
$select ->from(array('p' => 'product'));
$select->joinLeft(array('pd' => 'product_description'), '', 'description');
$select->join(array('l' => 'language'), 'pd.language_id = l.language_id and code = \'en\' ON p.product_id = pd.product_id', 'description');

i think there should be better way to do that, because adding the ON-clause in the join of another table isn't that clean.