Regarding the statement below, sltrxid can exist as both ardoccrid and ardocdbid. I’m wanting to know how to include both in the NOT IN subquery.
SELECT * FROM glsltransaction A INNER JOIN cocustomer B ON A.acctid = B.customerid WHERE sltrxstate = 4 AND araccttype = 1 AND sltrxid NOT IN( SELECT ardoccrid,ardocdbid FROM arapplyitem)
Advertisement
Answer
I would recommend not exists
:
SELECT * FROM glsltransaction t INNER JOIN cocustomer c ON c.customerid = t.acctid WHERE ??.sltrxstate = 4 AND ??.araccttype = 1 AND NOT EXISTS ( SELECT 1 FROM arapplyitem a WHERE ??.sltrxid IN (a.ardoccrid, a.ardocdbid) )
Note that I changed the table aliases to things that are more meaningful. I would strongly recommend prefixing the column names with the table they belong to, so the query is unambiguous – in absence of any indication, I represented this as ??
in the query.
IN
sometimes optimize poorly. There are situations where two subqueries are more efficient:
SELECT * FROM glsltransaction t INNER JOIN cocustomer c ON c.customerid = t.acctid WHERE ??.sltrxstate = 4 AND ??.araccttype = 1 AND NOT EXISTS ( SELECT 1 FROM arapplyitem a WHERE ??.sltrxid = a.ardoccrid ) AND NOT EXISTS ( SELECT 1 FROM arapplyitem a WHERE ??.sltrxid = a.ardocdbid )