Zend DB Join Multiple Tables with Multi Count

I am trying to join two tables to my maintable as follows:

bannermanager_banner = main_table

bannermanager_banner_click = table I want a record count where maintable.id = banner_click_table.banner_id

bannermanager_banner_impression = table I want a record count where maintable.id = banner_impression_table.banner_id

I have tried many things, the closed I have got is this:

    $this->getSelect()
        ->join(array('bc' => $this->getTable('vortex_bannermanager/banner_click')), 'bc.banner_id=main_table.id', array('banner_click_count' => 'count(bc.id)'))
        ->join(array('bi' => $this->getTable('vortex_bannermanager/banner_impression')), 'bi.banner_id=main_table.id', array('banner_impression_count' => 'count(bi.id)'))
        ->group('main_table.id');
开发者_StackOverflow

But the counts are returning the total of both each =S

Thanks for any pointers.

By nature of count and group by you can't really achieve it this way. If you need multiple counts in one query you need to use subquery's.