CODEWITHSUNDEEP
mysqlsqlyii
Dima Knivets
Dima Knivets

Reputation: 2538

Matching all values in IN clause

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,
    ));
}

Upvotes: 60

Views: 37451

Answers (2)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171599

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.

SQL Fiddle Example

If you want to find the items that have all of a specific set of CategoryIDs and no others, this is one way you can approach it:

select a.ItemID
from (
    select ItemID, count(distinct CategoryID) as CategoryCount
    from [dbo].[ItemCategory]
    where CategoryID in (5,6,7,8)
    group by ItemID
    having count(distinct CategoryID) = 4 
) a
inner join (
    select ItemID, count(distinct CategoryID) as CategoryCount
    from [dbo].[ItemCategory]
    group by ItemID
) b on a.ItemID = b.ItemID and a.CategoryCount = b.CategoryCount

SQL Fiddle Example

If you prefer, you could do it with a subquery:

select ItemID 
from ItemCategory 
where ItemID in (
    select ItemID 
    from ItemCategory 
    where CategoryID in (5,6,7,8) 
    group by ItemID 
    having count(distinct CategoryID) = 4
) 
group by ItemID 
having count(distinct CategoryID) = 4

SQL Fiddle Example

Upvotes: 88

Ryan
Ryan

Reputation: 14659

 SELECT ItemID
     FROM ItemCategory
        WHERE (
               (CategoryID = 5) OR 
               (CategoryID = 6) OR 
               (CategoryID = 7) OR 
               (CategoryID = 8)
              )
     GROUP BY ItemID
 HAVING COUNT(DISTINCT CategoryID) = 4

Upvotes: 5

Related Questions