title: SQL statement to select group containing all of a set of values
body: <p>In SQL Server 2005, I have an order details table with an order id and a product id.  I want to write a sql statement that finds all orders that have all the items within a particular order.  So, if order 5 has items 1, 2, and 3, I would want all other orders that also have 1, 2, and 3.  Also, if order 5 had 2 twice and 3 once, I'd want all other orders with two 2s and a 3.</p>
  
  <p>My preference is that it return orders that match exactly, but orders that are a superset are acceptable if that's much easier / performs much better.</p>
  
  <p>I tried a self-join like the following, but that found orders with <em>any</em> of the items rather than <em>all</em> of the items.</p>
  
  <pre><code>SELECT * FROM Order O1
  JOIN Order O2 ON (O1.ProductId = O2.ProductId)
  WHERE O2.OrderId = 5
  </code></pre>
  
  <p>This also gave me duplicates if order 5 contained the same item twice.</p>