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

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?

CodePudding user response：

Just a guess since you don't specify what resultset you desire. And surely there is another table that you did not include that represents "orders" - perhaps named SALESORDERS?

Assuming that, then I suggest:

select ord."ORDER" -- a terrible idea to use reserved words as names
  from dbo.SALESORDERS as ord 
where not exists (select * from dbo.SALESORDERLINES as ordlines 
       where ord."ORDER" = ordlines."ORDER" and ordlines.ITEM = 'FEE')
order by ...;

Certainly there are other ways. EXCEPT comes to mind.

CodePudding user response：

Try this :

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