title: Zend_Db subquery
body: <p>I've been trying to construct a sql query with ZendFW, but I cant seem to get it to function like I want to (or function at all). This is the query that works that I'm trying to build with zend_db select()</p>
  
  <pre><code>SELECT tc.trip_title, td.ID, td.trip_id, 
    (SELECT count(*) FROM 'trips_invites' ti 
     WHERE ti.destination_id=td.ID AND ti.accepted ='NR') AS "pending_invites" 
  FROM `trips_current` AS `tc`, `trips_data` AS `td` 
  WHERE (tc.ID=td.trip_id) AND (tc.creator_id = '1') 
  ORDER BY `trip_id` ASC 
  </code></pre>
  
  <p>What I can't figure out is how to properly get that subquery in there, and nothing I try seems to work.</p>
  
  <p>Any help would be greatly appreciated!</p>
  
  <p>Thanks!</p>
  
  <p><strong>Edit/Answer:</strong> If anyone will ever have a similar problem, based on the suggestion below I re-worked by query in the following way:</p>
  
  <pre><code>SELECT `tc`.`trip_title`, `td`.`ID`, `td`.`trip_id`, count(TI.ID)
  FROM `trips_current` AS `tc` 
  INNER JOIN `trips_data` AS `td` ON td.trip_id = tc.ID 
  LEFT JOIN trips_invites AS TI ON ti.destination_id = td.id
  WHERE tc.creator_id = 1  AND ti.accepted='NR'
  GROUP BY td.id
  ORDER BY `trip_id` ASC
  </code></pre>
  
  <p>which using ZendFW I created this way: </p>
  
  <pre><code>$select = $this-&gt;dblink-&gt;select() 
  -&gt;from(array('tc' =&gt; 'trips_current'),
        array('trip_title'))
  -&gt;join(array('td' =&gt; 'trips_data'), 
  'td.trip_id = tc.id',                   
        array('ID','trip_id'))
  -&gt;joinLeft(array('ti'=&gt;'trips_invites'),
       'ti.destination_id = td.id',
       array('COUNT(ti.id)'))
  -&gt;where('tc.creator_id =?',1)
  -&gt;group('td.id')
  -&gt;order('trip_id');
  </code></pre>