Generally, We use SQL Where .. IN Statement to determine whether a specified value matches ANY value in a sub-query or a list. But sometimes we require to get data which have ALL specified values, not ANY value. In this post, we'll see how to accomplish it. Let's take an example, Consider following data in Orders table
| OrderID | ProductID |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 3 |
| 3 | 2 |
| 3 | 3 |
| 4 | 3 |
| 5 | 2 |
| 5 | 4 |
--------------------------------
SQL Query:
Select distinct OrderID
From Orders
Where ProductID in (2,3)
--------------------------------
The above query selects OrderIDs which has any specified ProductID (2 or 3 or both)
Output:
1
2
3
4
5
Now, we want to select OrderIDs which have ALL specified values (2 and 3 both). See following SQL query:
--------------------------------
Select OrderIDFrom Orders
Where ProductID in (2,3)
group by OrderID
having count(*) = 2--------------------------------
Output:
1
3
Which is expected. In above query, We are grouping by OrderID, counting and select if count is equal to total number of items in the specified values. If there are 5 items in IN condition then you have to set having count(*) = 5.
Hope, It saves your time. Let me know how you are doing to do this.