title: Finding all combinations of two products that never were ordered together
body: <p>I have an array in SQL containing all orders and all products from those orders and I need to find all pairs of products that have never been ordered together in one order.</p>
  
  <p>I started with something like this</p>
  
  <pre><code>SELECT a.orderid, a.productid, b.OrderID, b.ProductID from [Order Details] a cross join [Order Details] b
  except  
  SELECT a.orderid, a.productid, b.OrderID, b.ProductID from [Order Details] a cross join [Order Details] b
  where a.ProductID=b.ProductID
  </code></pre>
  
  <p>but I don't know how to eliminate rest of the possible pairs so that I have only the ones I need left.</p>
  
  <p>EDIT:
  Modified the query a bit, went for a diffrent approach. I'm getting closer but still don't know how to get rid of repetitions like ab and ba</p>
  
  <pre><code>select p1.productid, p2.productid from products p1 join products p2 on p1.productid&lt;&gt;p2.productid
  except
  select a.productid, b.productid from [Order Details] a join [Order Details] b on a.ProductID&lt;&gt;b.ProductID
  where a.OrderID=b.OrderID
  </code></pre>