Re: [fw-db] Queries with LEFT JOIN &COUNT(table.column) possible with Zend_Db_Query?

I believe it's the second from() call that is creating the inner join. Also, you may want to place your SQL expressions in a Zend_Db_Expr object.

This might work:

if($doCount)
{
    $select->joinLeft(

        'table2',                    // table to join

        'table1ID = table2ID', // how to join table, using "ON"

        array(

            'someCount' => new Zend_Db_Expr('COUNT(table2ID)')

        )
    ->group('table1.table1ID');

}

-Hector

On Tue, Mar 24, 2009 at 9:16 AM, lightflowmark <1@lightflowinterrupted.com> wrote:

Just bumping this up to see if anyone has any input into this - I can't find
a way around this at all, and I'd have thought that queries like this were
quite common:

SELECT table1.table1ID,table1.someCol1,COUNT(table2ID)  as someCount
FROM table1
LEFT JOIN table2 USING (table1ID)
GROUP BY table1.table1ID

which produces
+----------+---------------+-----------+
| table1ID | someCol1      | someCount |
+----------+---------------+-----------+
|        1 | table 1 row 1 |         3 |
|        2 | table 1 row 2 |         0 |
|        3 | table 1 row 3 |         1 |
+----------+---------------+-----------+

Thanks - I won't bump this again!
--
View this message in context: http://www.nabble.com/Queries-with-LEFT-JOIN--COUNT%28table.column%29-possible-with-Zend_Db_Query--tp22557518p22683989.html

Sent from the Zend DB mailing list archive at Nabble.com.