John K John K - 3 months ago 8
SQL Question

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.

Joe Joe
Answer

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