[fw-db] SELECT WHERE IN (SELECT) question

Hi,

I've got a bit of MySQL which I cannot fully replicate using select().

SELECT
DISTINCT `parent`.*
FROM
`categories` AS `parent`,
`categories` AS `node`
WHERE
(node.lhs BETWEEN parent.lhs AND parent.rhs)
AND
(node.category_id
IN (
SELECT
DISTINCT category_id
FROM
articles
WHERE
published = 1
)
)
ORDER BY
`parent`.`lhs` ASC

So far I've come up with this:

<?php
class Categories extends Zend_Db_Table_Abstract
{

protected $_name = 'categories';

public function fetchCategories()
{

$select = parent::select();
$select->setIntegrityCheck(false);

$select->distinct()
->from (array('parent' => $this->_name),
array('*')
)
->join (array('node' => $this->_name), '', '')
->where('node.lhs BETWEEN parent.lhs AND parent.rhs')
->where('node.category_id IN (SELECT DISTINCT category_id FROM
_productstest WHERE published = 1)')
->order(array('parent.lhs'));

return parent::fetchAll($select);
}

It works, but is there any way I can improve on the final WHERE clause with
the IN predicate?

Thanks!
--
View this message in context: http://www.nabble.com/SELECT-WHERE-IN-%28SELECT%29-question-tp22566627p22566627.html
Sent from the Zend DB mailing list archive at Nabble.com.