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?

Answer 1

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.