select orders with all products in ( product-list ) without using dynamic SQL

Vote count:

0

Given this OrderDetail table:

OrderId int (FK references Order)

ProductId int (FK references Product)

find all orders having all of the products in product-id-list ( productid1, productid2, ... productid-N).

If we know the number of distinct products in product-id-list we can hard-code the query and do a group by OrderId having count(*) = number of products in product-id-list.

But if the number of products in the list is not known in advance, what is the SQL92-compatible way to find all orders that contained all of the desired products, without building a dynamic SQL string and executing it?

asked 1 min ago

Tim

3,004

select orders with all products in ( product-list ) without using dynamic SQL