Issue Type: Improvement Created: 2008-11-04T15:40:17.000+0000 Last Updated: 2012-11-20T20:53:28.000+0000 Status: Closed Fix version(s): Reporter: Michael Virnstein (gigamouse) Assignee: None Tags: - Zend_Db_Select
Related issues: Attachments:
Assume i have the following mysql tables:
<pre class="highlight"> 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:
<pre class="highlight"> 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:
<pre class="highlight"> $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.
Posted by Rob Allen (rob) on 2012-11-20T20:53:28.000+0000
Bulk change of all issues last updated before 1st January 2010 as "Won't Fix".
Feel free to re-open and provide a patch if you want to fix this issue.
Have you found an issue?
See the Overview section for more details.