SQL ALL IN clause

Question

I have been searching for this, but didn't find anything special.

Is it possible to have an SQL query which will act like ALL IN? To better explain, Here is a table structure.

Orders table
OrderItem table (having several columns, but mainly ProductID, OrderID)
ProductGroup table (several columns, but mainly GroupID and ProductID)

I want to write a query which will select all those order which belongs to a specific ProductGroup. So if I have a group named "XYZ" with ID = 10. It has One ProductID in it. Say ProductID01

An order came in with two order items. ProductID01 and ProductID02. To find all orders in the specific Product Group I can use a simple SQL like

SELECT bvc_OrderItem.ProductID, bvc_OrderItem.OrderID
From bvc_OrderItem
INNER JOIN bvc_Product_Group_Product with (nolock) ON bvc_OrderItem.ProductID = bvc_Product_Group_Product.ProductID 
WHERE bvc_Product_Group_Product.GroupID = 10

Or I can write using an IN Clause

SELECT bvc_OrderItem.ProductID, bvc_OrderItem.OrderID
From bvc_OrderItem
WHERE ProductID IN (
    SELECT ProductID FROM bvc_Product_Group_Product WHERE GroupID=10
)

However, This will return all orders where one or more ProductIDs are part of the product group. I need to return the order row ONLY if ALL of the order items are part of the Product Group

So basically, I need an IN Clause which will considered matched if ALL of the values inside IN Clause matches the rows in bvc_OrderItem.

Or if we are using the Join, then the Join should only succeed if ALL rows on the left have values in the corresponding right table.

If I could write it more simply, I would write it like this

Select ID FROM Table WHERE ID IN (1, 2, 3, 4)

and if the table contains all rows with ids 1,2,3,4; it should return success. If any of these IN values is missing, it should return false and nothing should be selected.

Do you think it is possible? Or there is a workaround to do that?

Maybe you should reword your condition as 'orders, for which do not exist any rows, which do not belong to group X'? — Arvo, Apr 07 '15 at 12:02
This sounds like [relational division](https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/) — Damien_The_Unbeliever, Apr 07 '15 at 12:09
I don't understand what you are trying to find. There are orders made up of one or more items, each item being a product which exists in a group. So your statement "To find all orders in the specific Product Group..." doesn't make sense. Orders are not related to Product Groups. Do you mean for an order that consists of product P belonging to group G, you want to see all other orders that consists only of products of group G? Or do you want to see all orders that consist of *any* product of group G? What are you looking for? — TommCatt, Apr 08 '15 at 17:51
I am sorry, maybe I was not clear enough. Actually, I want all order items belong to same group. So if order has SKU1 and SKU2, they both must be in ProductGroup A. — Sameers Javed, Apr 09 '15 at 12:55

score 4 · Answer 1 · answered Apr 07 '15 at 12:10

You can get the list of orders in a variety of ways, such as:

SELECT oi.OrderID
FROM bvc_OrderItem oi JOIN
     bvc_Product_Group_Product pgp
     ON oi.ProductID = pgp.ProductId AND
        pgp.GroupID = 10
GROUP BY oi.OrderID
HAVING COUNT(DISTINCT oi.ProductID) = (SELECT COUNT(*)
                                       FROM bvc_Product_Group_Product
                                       WHERE GroupID = 10
                                      );

Getting the specific products requires an additional join. In most cases, the list of orders is more useful.

The problem with your ALL IN syntax is that it doesn't do what you want. You want to select orders. The syntax:

SELECT bvc_OrderItem.ProductID, bvc_OrderItem.OrderID
From bvc_OrderItem
WHERE ProductID ALL IN (SELECT ProductID
                        FROM bvc_Product_Group_Product
                        WHERE GroupID = 10
                       )

This doesn't specify that you intend for the grouping to be by OrderId, as opposed to some other level.

More fundamentally, though, the SQL language is inspired by relational algebra. The constructs of SELECT, JOIN, WHERE, and GROUP BY directly relate to relational algebra fundamental constructs. The notion of ALL IN -- although sometimes useful -- can be expressed using the more basic building blocks.

WHERE ProductID ALL IN ...... Which SQL server version does it works? I already tried it on 2008 and 2008, but that doesn't seems to be a valid option for them. — Sameers Javed, Apr 07 '15 at 12:36
Also, using the Grouping with Count(DISTINCT oi.Product) thing, that might not work as well. Since a product group may have 100 Products in it, and order may have two or 4 items in it. We need to make sure that the Order Items are all part of the product group. So if both or all order items are part of product group, we will consider it as a match. If any of them is NOT part of the group, we will consider it as NON-MATCH. Thanks — Sameers Javed, Apr 07 '15 at 12:44

Giorgi Nakeuri · Answer 2 · 2015-04-07T14:53:00.443

You can do it by this tricky statement:

DECLARE @Items TABLE
    (
      OrderID INT ,
      ProductID INT
    )
DECLARE @Groups TABLE
    (
      ProductID INT ,
      GroupID INT
    )

INSERT  INTO @Items
VALUES  ( 1, 1 ),
        ( 1, 2 ),
        ( 2, 1 ),
        ( 3, 3 ),
        ( 3, 4 )

INSERT  INTO @Groups
VALUES  ( 1, 10 ),
        ( 2, 10 ),
        ( 3, 10 ),
        ( 4, 15 )


SELECT  OrderID
FROM    @Items i
GROUP BY OrderID
HAVING  ( CASE WHEN 10 = ALL ( SELECT   gg.GroupID
                               FROM     @Items ii
                                        JOIN @Groups gg ON gg.ProductID = ii.ProductID
                               WHERE    ii.OrderID = i.OrderID ) THEN 1
               ELSE 0
          END ) = 1

Output:

OrderID
1
2

Also(this is better):

SELECT  OrderID
FROM    @Items i
        JOIN @Groups g ON g.ProductID = i.ProductID
GROUP BY OrderID
HAVING  MIN(g.GroupID) = 10
        AND MAX(g.GroupID) = 10

Hi, Thank you for your reply. However, it will return true if even one Product belongs to the group. we need it that if ALL products belong to the group. I tested it, and it returns orderid even if One ProductID belongs to product group. We need it to NOT return OrderID if only one item belongs to group. But only when ALL order items (ProductIds) belongs to the group. — Sameers Javed, Apr 09 '15 at 12:57
@SameersJaved, you are not right. In my data there is Order =3 with 2 products(3, 4) 3 is group 10 and 4 is group 15. Order = 3 is not returned with my query. — Giorgi Nakeuri, Apr 09 '15 at 13:04

SQL ALL IN clause

2 Answers2

Linked