SQL NOT IN Subquery?

Question

So I have the following query (see below) that I wrote for a co-worker:

SELECT
        t.tender_id as "Tender ID",
        t.check_number as "Check Number",
        t.check_type_id as "Check Type",
        t.server_id as "Server ID",
        t.cashier_id as "Cashier ID",
        t.terminal_id as "Terminal ID",
        t.tendered_date_time as "Tendered Date and Time",
        t.tender_amount as "Tender Amount",
        t.change_amount as "Change Amount",
        g.account_name as "Account Name",
        g.account_number as "Account Number"
FROM CheckTender AS t
INNER JOIN AcountActivity AS g
        ON t.check_number = g.check_number
WHERE t.tender_id NOT in (5,14,4,9,15,16);

A check number can have multiple tender_IDs. So, for example, you could have three rows for check number 20001, all with different tender_IDs.

Initially, he just wanted any entries where the tender_id was 5,14,4,9,15,16 removed. But then we discovered that we need to modify the where criteria.

It needs to be updated so that IF a check number has a tender ID of 5,14,4,9,15,16, ALL instances of that check number are removed. I know this is most likely a subquery, but I have been beating my head against it all morning without being able to figure it out. :(

Answer 1

You can use EXISTS for this:

SELECT
        t.tender_id as "Tender ID",
        t.check_number as "Check Number",
        t.check_type_id as "Check Type",
        t.server_id as "Server ID",
        t.cashier_id as "Cashier ID",
        t.terminal_id as "Terminal ID",
        t.tendered_date_time as "Tendered Date and Time",
        t.tender_amount as "Tender Amount",
        t.change_amount as "Change Amount",
        g.account_name as "Account Name",
        g.account_number as "Account Number"
FROM CheckTender AS t
INNER JOIN AcountActivity AS g
        ON t.check_number = g.check_number
WHERE NOT EXISTS (  SELECT * FROM CheckTender WHERE check_number =  t.check_number
                    AND tender_id in (5,14,4,9,15,16))