Mysql - WHERE key list not in subquery

Question

I have a query like this:

SELECT DISTINCT(obt.oid) AS oid, o.borrower_email
FROM `order_borrower_tracker` obt 
LEFT JOIN `orders` o ON obt.oid=o.oid
WHERE obt.date_acknowledged_edelivery = 0 
AND 18 NOT IN 
    (SELECT DISTINCT(new_status) AS new_status 
    FROM `order_status_history` 
    WHERE oid=obt.oid)

Which works, but now I need to update this query so that not only "18" is not in the subquery, but also a few other numbers ... something like

SELECT DISTINCT(obt.oid) AS oid, o.borrower_email
FROM `order_borrower_tracker` obt 
LEFT JOIN `orders` o ON obt.oid=o.oid
WHERE obt.date_acknowledged_edelivery = 0 
AND (18,27,29,41,53) NOT IN 
    (SELECT DISTINCT(new_status) AS new_status 
    FROM `order_status_history` 
    WHERE oid=obt.oid)

but obviously, "(18,27,29,41,53) NOT IN" does not work.

What is a good alternative to achieve this? No AND input for each number

Answer 1

Use the trick with LEFT JOIN and NULL to find orders that do not have records with the listed statuses:

SELECT DISTINCT(obt.oid) AS oid, o.borrower_email
FROM `order_borrower_tracker` obt 
LEFT JOIN `orders` o 
    ON obt.oid=o.oid
LEFT JOIN `order_status_history` h
    ON h.oid=obt.oid AND h.new_status IN (18,27,29,41,53)
WHERE obt.date_acknowledged_edelivery = 0 
AND h.oid IS NULL