title: Exclude SQL query results with multiple conditions - single table
body: <p>I ran into a problem with a query I am using from another question that was previously asked. I did not want to add to that question, so I am just starting a new one. This scenario is with SQL Server 2008.</p>
  
  <pre><code>Order Number    Order Line    Package    Part Number    Size    Cost    Reviewed
     0001              1           1           A1          S      22.5      Yes
     0001              1           1           B2          M      33.1      Yes
     0001              1           1           C3          L      11.2      Yes
     0001              1           2           A1          XL     15.0      Yes
     0001              1           3           A2          M      12.0      Yes
     0001              2           1           D1          S      42.9      Yes
     0002              1           1           B4          L      72.5      No
     0002              1           2           A7         XXL     66.7      No
     0002              2           1           C1          XL     11.8      Yes
     0002              2           1           B1          S      22.3      Yes
     0003              1           1           A1          L      55.2      Yes
  </code></pre>
  
  <p>I would like to select Order Number, Order Line, and Package. I have to search by Part Number, Size, Cost, and if it was Reviewed. This table has around 30,000 orders, so there will be multiple results (which is what I want). I have to exclude results that have an Order Line where Package &lt;> 1. In other words, I do not want an instance of an order line if it has a package &lt;> 1. An order is split up into several rows, and each row can have multiple packages attached to it. Below is the query that works correctly.</p>
  
  <pre><code>SELECT
     ORDERNumber,
     ORDERLine,
     Package
  FROM
     orders a
  WHERE  NOT EXISTS (SELECT *
                       FROM orders AS b
                      WHERE a.ordernumber = b.ordernumber
                            AND a.orderline = b.orderline
                            AND b.Package &lt;&gt; 1)
  GROUP BY
  ORDERNumber,
     ORDERLine,
     Package 
  </code></pre>
  
  <p>Here are the results of the above example query (which correctly meets my requirements)...</p>
  
  <pre><code>ORDERNUMBER     ORDERLINE     PACKAGE 
  0001            2             1 
  0002            2             1 
  0003            1             1 
  </code></pre>
  
  <p>The problem I am having occurs when I try to add multiple conditions. For example...</p>
  
  <pre><code>SELECT
     ORDERNumber,
     ORDERLine,
     Package
  FROM
     orders a
  WHERE (REVIEWED = 'Yes' AND SIZE = 'L') OR (REVIEWED = 'Yes' AND SIZE = 'S')
  AND  NOT EXISTS (SELECT *
                       FROM orders AS b
                      WHERE a.ordernumber = b.ordernumber
                            AND a.orderline = b.orderline
                            AND b.Package &lt;&gt; 1)
  GROUP BY
  ORDERNumber,
     ORDERLine,
     Package
  </code></pre>
  
  <p>Gives me the following results (which does NOT meet my requirements)...</p>
  
  <pre><code>ORDERNUMBER     ORDERLINE     PACKAGE 
  0001            1             1 
  0001            2             1 
  0002            2             1 
  0003            1             1 
  </code></pre>
  
  <p>Order number 0001, Order Line 1 is not valid because it has a 2 and 3 values for Package. It looks like there is something wrong with my order of conditions? I have tried playing around with these conditions, but I am not getting the results I want. I know this can be done with two separate queries and a UNION statement, but is there a way I can do multiple conditions with only one query for this scenario? Thanks.</p>