Sub Query at join not coming in right way

Question

Hiii, Currently I am stuck with a problem. I am finding no way to remove quotes generated by the zend on query generation of a sub query which is placed in a join operation. The $selectInnerQuery creates the sub query which is used in the join operation this is called as inneranswer table. This is used to join with answer table. $select is used for the final query. Please help me on this...

$selectInnerQuery= $sql->select()->from(array('answer' => 'tblanswer'))->columns(array('aid'        =>  new Expression('answer.aid'),'count' => new Expression('count(answer.qid)')));


$innerstatement = $sql->getSqlStringForSqlObject($selectInnerQuery);

$select = $sql->select()->from(array('answer' => 'tblanswer'))->columns($fieldsToSelect);
$select->join(array('inneranswer' =>  $innerstatement), 'inneranswer.aid = answer.aid', array(),'inner');

The query that I am getting from zend is

SELECT `answer`.* FROM `tblanswer` AS `answer` 
inner join `SELECT answer.aid AS ``aid``, count(answer.qid) AS ``count`` FROM ``tblanswer`` AS ``answer`` WHERE answer.qid !=0 GROUP BY answer.qid, answer.baid` AS `inneranswer` ON `inneranswer`.`aid` = `answer`.`aid` 

I have tried new Expression but it does not work in join operation.

Answer 1

When you are specifying your $innerstatement on joining, specify as like "{$innerstatement}", this may solve your quotes problem and also check your inner statement query returns sql query or it returns as object.

Answer 2

Came across the same issue today. Join table array should contain Select object instead of select query.

$selectObj = $sql->select()->from(array('answer' => 'tblanswer'))->columns(array('aid'        =>  new Expression('answer.aid'),'count' => new Expression('count(answer.qid)')));
$select = $sql->select()->from(array('answer' => 'tblanswer'))->columns($fieldsToSelect);
$select->join(array('inneranswer' =>  $selectObj), 'inneranswer.aid = answer.aid', array(),'inner');