TSQL: Given a set of IDs, obtain all headers such that the set is included in the header’s children

I am looking for a query that would return all Order ID’s that contain a set of productIDs, but might include more products.

Data structure:

CREATE TABLE dbo.Order(OrderID int, OrderDesc nvarchar(100))
CREATE TABLE dbo.OrderDetail(DetailID int, OrderID int, ProductID int)
CREATE TABLE dbo.Product(ProductID INT, ProductDesc nvarchar(100))

​x
 
CREATE TABLE dbo.Order(OrderID int, OrderDesc nvarchar(100))CREATE TABLE dbo.OrderDetail(DetailID int, OrderID int, ProductID int)CREATE TABLE dbo.Product(ProductID INT, ProductDesc nvarchar(100))​

So given a set of productIDs, I want a query to return all orders that contain all (possibly more) products from the set.

Answer

You can use aggregation:

select od.orderid
from orderdetail od
where od.productId in (. . .)
group by od.orderid
having count(od.productid) = <n>;   -- <n> is the number of products in the list

 
select od.orderidfrom orderdetail odwhere od.productId in (. . .)group by od.orderidhaving count(od.productid) = <n>;   -- <n> is the number of products in the list​

This assumes that there are no duplicate products in an order. If that is possible, use count(distinct od.productid).

Advertisement

Answer