Is this a known issue with ACF

Query Of Queries syntax error. Encountered "email not in ( select. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,StackTracejava.sql.SQLException

. The Adobe documentation claims

NOT IN

is fine https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/using-query-of-queries/query-of-queries-user-guide.html (search in conditional) . but I cant get it to fly locally and this gist https://trycf.com/gist/91a2db7d0767096bc1fc9d93f8caba93/lucee5?theme=monokai seems to demonstrate thjat it is fine in Lucee and Railo but not in any version of ACF although I am sure I have used the construct in the past.

looks like it doesn't support subselects (maybe?)

I think your right, oh this is going to be very frustrating

This will work:

// add the pews.id into a list and then use in QoQ.
pewsids = valueList(pews.id);    
result = queryExecute("select * from news where id not in (:pewsids)",{
    pewsids: {
        value: pewsids,
        sqltype: "integer",
        list: true
    }
},{dbtype="query"});

Yup I was there just struck me as strange I had never noticed it before

Can you use queryFilter()?

probably would need to compare performance

As a workaround have you tried to write it as a temp file in XML or JSON format?

@Evil Ware as a workaround to what?

To be clear, the docs are wrong (ping @Mark Takata (Adobe) / @saghosh). This - from https://helpx.adobe.com/coldfusion/developing-applications/accessing-and-using-data/using-query-of-queries/query-of-queries-user-guide.html - is not true: " In addition to being more legible when working with long lists, the IN conditional can contain another SELECT statement". No it cannot. I recommend ppl actively vote for https://tracker.adobe.com/#/view/CF-3861312, and/or actively raise tickets for features you think are missing from CFML.