Matching all values in IN clause

Question

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

What result are you after? AND is a boolean operator; what do you want it to do with a list of numbers? — Braiba, Jul 24 '12 at 17:24
Not sure what you are asking for here. The IN when used in a WHERE clause will give you all rows where the field value amtches one of those values. It is not possible that a filed could have ALL those values, so I don;t know what you are looking for from this theoretical AND() function. — Mike Brant, Jul 24 '12 at 17:25
I'm using it to list rows from db, based on criteria. This criteria is a list of taxonomy ids. — Dima Knivets, Jul 24 '12 at 17:26
@RPM citation needed. I think you may be confusing the use of IN on a subquery, which is slow because the subquery will get run against every record. I've never had any issue with using IN on a list of constants; if anything I expect it would be faster. — Braiba, Jul 24 '12 at 23:17
In fact, the accepted answer here shows IN to be about 2-3 times faster than OR: http://stackoverflow.com/questions/782915/mysql-or-vs-in-performance — Braiba, Jul 24 '12 at 23:20

D'Arcy Rittich · Accepted Answer · 2023-01-14T13:34:23.853

85

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

edited Jan 14 '23 at 13:34

answered Jul 24 '12 at 17:22

D'Arcy Rittich

167,292
40
290
283

What if i don't know this number? – Dima Knivets Jul 24 '12 at 17:24
7

@DimaKnivets Under what circumstance would you not know it? These kinds of queries need to be created dynamically. – D'Arcy Rittich Jul 24 '12 at 17:26
For example, if the value is passed dynamically from the GET request – Dima Knivets Jul 24 '12 at 17:27
1

@DimaKnivets You parse the querystring parameter and split out the unqiue entries which you then use to create the parameterized `IN` clause, and the count of items is used to create the `HAVING` clause. – D'Arcy Rittich Jul 24 '12 at 17:29
`having count(distinct CategoryID)` condition is perfect! +1 – Bora Jun 29 '15 at 12:12
2

Warning: This doesn't really work if there's an item that is included in categories `[5,6,7,8]` plus any other category. – Julian J. Tejera Sep 12 '16 at 21:22
@julian sure it does. The WHERE clause makes it ignore any other categories you haven't explicitly asked about. – D'Arcy Rittich Sep 14 '16 at 05:16
Can't this be changed to dynamic by doing: changing `having count(distinct CategoryID) = 4` to `having count(distinct CategoryID) = (select count(distinct id) from category)`; if there was a category table? – stackFan Apr 11 '18 at 17:46
1

@stackFan No, 4 is the number of unique items in your `WHERE` clause, not in the table itself. – D'Arcy Rittich Apr 12 '18 at 18:57
Perfect, but do you have something easier for me, too? `SELECT * FROM ItemCategory WHERE ItemID = (select ItemID from ItemCategory where CategoryID in (5,6,7,8) group by ItemID having count(distinct CategoryID) = 4) GROUP BY CategoryID HAVING COUNT(*) = 4` I want only the elements which contains **exactly** the 4 category id's. Not more and not less. Exact this 4. – Patrick Jul 11 '19 at 13:36
@Patrick thats exactly what im looking for. did u find the solution? – Diego Jan 13 '23 at 13:28
1

@Diego see my update above – D'Arcy Rittich Jan 14 '23 at 13:21

score 5 · Answer 2 · edited Jan 25 '18 at 13:27

5

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

edited Jan 25 '18 at 13:27

Tvaroh

6,645
4
51
55

answered Jul 24 '12 at 17:38

Ryan

14,392
8
62
102

4

Changing `HAVING COUNT (CategoryID)` to `HAVING COUNT (DISTINCT CategoryID)` will help prevent a false positive if one of the values is present twice (as in RedFilter's answer above). – Byson Dec 16 '15 at 12:15

Matching all values in IN clause

2 Answers2

Linked

Related