CODEWITHSUNDEEP
sql
John K
John K

Reputation: 57

Exclude SQL query results - single table

I am used to creating basic queries, so I am stuck on a rather complex one. 

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

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 are multiple results (which is what I want). I got the easy part down, and it works correctly. Example:

SELECT
ORDER Number,
ORDER Line,
      Package
FROM TABLE
WHERE (Part Number='A1'
       AND SIZE='S'
       AND Cost='22.5'
       AND Reviewed='Yes')
GROUP BY ORDER Number,
         ORDER Line,
         Package 
HAVING count(ORDER Number)=1


Order Number    Order Line    Package
   0001              1           1

Here is my challenge. I have to exclude results that have an Order Line where Package <> 1. So the result for my example above will be excluded, because for Order Number 0001 - Order Line 1 contains a Package of 2 and 3. When this exclusion is applied, the only valid results in the table I provided should be...

Order Number    Order Line    Package
   0001              2           1
   0002              2           1
   0003              1           1

Do not worry about null values. Performance is a concern (obviously), as it is a large table. I have looked around, but I have not found any solid solutions for this yet. Your guidance will be appreciated.

Upvotes: 3

Views: 291

Answers (3)

Menelaos
Menelaos

Reputation: 25727

Using a derived table if you use MYSQL. Basically, this is efficient because it creates a temporary small derived table of all orderline(s) that have have packages <> 1. Then we join to the main query with NOT condition.

SELECT
d1.ORDERNumber,
d1.ORDERLine,
d1.Package
FROM data d1,    
(select distinct ORDERLINE, 
 PACKAGE from data 
 where NOT Package = 1)
as d2    
WHERE (PartNumber='A1'
       AND SIZE='S'
       AND Cost=22.5
       AND Reviewed='Yes' 
       AND NOT d1.orderline = d2.orderline)
GROUP BY ORDERNumber,
         ORDERLine,
         Package 
HAVING count(ORDERNumber)=1

http://www.sqlfiddle.com/#!2/b391e/8

Upvotes: 0

Joe
Joe

Reputation: 6827

This will exclude records with a matching order_number and order_line that have a package other than 1:

SELECT
   ORDER_Number,
   ORDER_Line,
   Package
FROM 
   aTABLE
WHERE  NOT EXISTS (SELECT 1
                     FROM atable AS b
                    WHERE atable.order_number = b.order_number
                          AND atable.order_line = b.order_line
                          AND b.Package != 1)
GROUP BY ORDER_Number,
     ORDER_Line,
     Package

Upvotes: 3

Jerry
Jerry

Reputation: 1785

This might be a little hackish and not promising good performance, but I've used something like this before.

SELECT ORDERNumber,
       ORDERLine
FROM orders
GROUP BY ORDERNumber,
         ORDERLine 
HAVING SUM(CASE WHEN package = 1 THEN 0 ELSE 1 END)= 0
ORDER BY ordernumber,
         orderline

sqlFiddle

The case when part should be common across MySql, SqlServer, and Oracle.

Upvotes: 1

Related Questions