On Wed, Sep 22, 2010 at 3:12 PM, My Lists <[email protected]> wrote: > Hello . Please, is there any good soul here that can help me to write > a specific query to zend_db? > > I can deal with joins on zend_db very well but now i need some > subquery together with joins. Following is my query. > > SELECT `ms`.*, COUNT(mi.id) AS `mailingItemCount` , msr.sent AS > `sent` , msr.view AS `view` , msr.click AS `click` > FROM `mailingSchedule` AS `ms` > LEFT JOIN `mailingItem` AS `mi` ON mi.mailingSchedule__id__FK=ms.id > INNER JOIN > ( > SELECT > SUM(IF(msr.bounce=0,1,0)) AS sent, > SUM(msr.view) AS view, > SUM(msr.click) AS click > FROM mailingSchedule AS ms > LEFT JOIN > mailingScheduleResult AS msr ON msr.mailingSchedule__id__FK=ms.id > GROUP BY ms.id > ) as msr > > GROUP BY ms.id > > > ps: i using mysql. > > Thanks a lot in advance. > > Eduardo >
The times that I've needed to build subqueries like that, I built the subquery on its own first, then passed it as a string argument inside Zend_Db_Expr when I was building the outer query. I'm not sure what you would have to do specifically in the case above, though, since the subquery is being used as a derived table rather than an expression. Andrew
