Zend Frameworks 1.12: how to join custom subquery table without returning results

Question

I need to create the following query (this is just an example) using Zend Framework 1.12 as part of a larger query:

SELECT `s`.* 
FROM `s`
INNER JOIN SELECT id FROM table where id = 13 AS `t`

Here's my attempt:

$query = $this->getDbTable()->select()
        ->from($this->getDbTable(), array('*'))
        ->join(array('t' => new Zend_Db_Expr('(SELECT id FROM tables where id = 13)')), array())
        ->setIntegrityCheck(false);

However, this is the output:

SELECT `students`.*, `t`.* 
FROM `students`
INNER JOIN SELECT id FROM tables where id = 13 AS `t`

I do not require the t.* from the select since the t table will be used in other ways within the complex query.

Do you know how not to select the t.* but still have the inner join using the subquery?

score 1 · Accepted Answer · answered Feb 24 '15 at 11:03

The Zend_Db_Select->join command is build of two/three parameters.

table name (with possible alias)
on part (ON table1.a = table2.a..)
and the columns you want to select (if you define no parameter here, you end up selecting all the possible fields "t`.*")

In your query you are missing the last parameter. If you assign another parameter to the join (see example below) you should get only the results from the table in your ->from(... part.

->join(array('t' => new Zend_Db_Expr('(SELECT id FROM tables where id = 13)')), array(), array())

Zend Frameworks 1.12: how to join custom subquery table without returning results

1 Answers1