How do I return a key value when a specific column value is NOT present?

Question

Here's a simplified example of my SALESORDERLINES table:

ORDER	LINE	ITEM
100	1	ITEMA
100	2	ITEMB
100	3	FEE
101	1	ITEMA
101	2	FEE
102	1	ITEMC
102	2	ITEMD
102	3	ITEME
103	1	ITEMA
103	2	FEE
104	1	ITEMB
104	2	ITEMC

The key values for the table are ORDER and LINE.

The last line item of each order is supposed to be item "FEE", but occasionally order entry forgets to include it. I'm trying to find every instance where they failed to include the fee on the order.

So for the example data above, I would want to return order numbers 102 and 104 only.

Any ideas?

Answer 1

Try this :

SELECT ORDER
FROM TableA
WHERE ORDER NOT IN (   
      SELECT ORDER
      FROM TableA
      WHERE ITEM = 'FEE'
      GROUP BY ORDER)
GROUP BY ORDER