SQL Select statement for NOT IN multiple values for groups of rows-CodePudding

I'm trying to use the NOT IN query but apply it to batches of rows where a column value is the same. SELECT * FROM CHECKOUTS WHERE ProductId NOT IN (2345) will return the row where the ProductName is Toy2 and Toy3 but I want it to not return Toy2 b/c the CheckoutId is the same as the Toy1.

Essentially I want the NOT IN query to run in batches of rows that have the same CheckoutId. Instead of returning the Toy2 and Toy3 I want it to return just Toy3.

CheckoutId	ProductId	ProductName
1234	2345	Toy1
1234	0987	Toy2
0123	8787	Toy3

Is this even possible? I feel like I need a completely new query parameters?

CodePudding user response：

You can solve the query in two steps, although they can be combined in one query. First step: find those checkout ids you don't want. Second step: get the checkout records without those ids.

SELECT * 
FROM CHECKOUTS 
WHERE 
    CheckoutId NOT IN (
        SELECT CheckoutId 
        FROM CHECKOUTS 
        WHERE ProductId IN (2345));