title: Using a subquery to remove grouped rows with specific value
body: <p>Please see:</p>
  
  <p><a href="http://sqlfiddle.com/#!18/479d1" rel="nofollow noreferrer">http://sqlfiddle.com/#!18/479d1</a></p>
  
  <p>I have these tables:</p>
  
  <pre><code>CREATE TABLE Item 
  (
      OrderNo varchar(10),
      ItemNo varchar(10)
  );
  
  CREATE TABLE Order1 
  (
      OrderNo varchar(10),
      Month varchar(10)
  );
  
  INSERT INTO Item (OrderNo, ItemNo)
  VALUES ('111','222'), ('111','333'),
         ('333','444'), ('333','222'), ('333','555'),
         ('444','222'), ('444','555');
  
  INSERT INTO Order1 (OrderNo, Month)
  VALUES ('111','Feb'), ('333', 'Jan'), ('444', 'May');
  </code></pre>
  
  <p>I am looking to return orders which don't have the item <code>'555'</code>. I figured this would be a subquery, but I just cant seem to get it right.</p>
  
  <p>I have tried:</p>
  
  <pre><code>SELECT 
      O.[OrderNo],
      O.[Month],
      I.[ItemNo]
  FROM 
      Order1 O
  JOIN 
      Item I ON O.[OrderNo] = I.[OrderNo]
  WHERE 
      O.[OrderNo] IN (SELECT I.[OrderNo]
                      FROM Item I
                      GROUP BY I.[OrderNo], I.[ItemNo]
                      HAVING I.[ItemNo] &lt;&gt; '555')
  </code></pre>
  
  <p>Which returns this result:</p>
  
  <pre>
  | OrderNo | Month | ItemNo |
  |---------|-------|--------|
  |     111 |   Feb |    222 |
  |     111 |   Feb |    333 |
  |     333 |   Jan |    444 |
  |     333 |   Jan |    222 |
  |     333 |   Jan |    555 |
  |     444 |   May |    222 |
  |     444 |   May |    555 |
  </pre>
  
  <p>But in this case I want to remove all orders with item <code>'555'</code>, so a result that looks like:</p>
  
  <pre>
  | OrderNo | Month | ItemNo |
  |---------|-------|--------|
  |     111 |   Feb |    222 |
  |     111 |   Feb |    333 |
  </pre>