Matching all values in IN clause
MysqlSqlYiiMysql Problem Overview
Is there a way to ensure all values in an IN
clause are matched?
Example:
I can use IN as: IN (5,6,7,8)
.
I need it to work like an AND
across multiple rows.
UPDATE: I need this to list companies from db that fit specified parameters. Companies and taxonomy are MANY TO MANY relation. I'm using Yii framework. And this is the code of my controller:
public function actionFilters($list)
{
$companies = new CActiveDataProvider('Company', array(
'criteria' => array(
'condition'=> 'type=0',
'together' => true,
'order'=> 'rating DESC',
'with'=>array(
'taxonomy'=>array(
'condition'=>'term_id IN ('.$list.')',
)
),
),
));
$this->render('index', array(
'companies'=>$companies,
));
}
Mysql Solutions
Solution 1 - Mysql
You can do something like this:
select ItemID
from ItemCategory
where CategoryID in (5,6,7,8) <-- de-dupe these before building IN clause
group by ItemID
having count(distinct CategoryID) = 4 <--this is the count of unique items in IN clause above
If you provide your schema and some sample data, I can provide a more relevant answer.
Solution 2 - Mysql
SELECT ItemID
FROM ItemCategory
WHERE (
(CategoryID = 5) OR
(CategoryID = 6) OR
(CategoryID = 7) OR
(CategoryID = 8)
)
GROUP BY ItemID
HAVING COUNT(DISTINCT CategoryID) = 4