[bug] Advanced Queries with subqueries don't work like they should.

[bug] Advanced Queries with subqueries don't work like they should.

  
Hi,

I tried to create an advanced query with a subquery. The positive (IN) query worked like it should, but the negative (NOT IN) showed no records, while it had to show all records where the Id is not in the subquery.
Worked:
SELECT {TABLE_A}.* FROM {TABLE_A} WHERE {TABLE_A}.[Id] IN (SELECT {TABLE_B}.[TableAId] FROM {TABLE_B})

Didn't work:
SELECT {TABLE_A}.* FROM {TABLE_A} WHERE {TABLE_A}.[Id] NOT IN (SELECT {TABLE_B}.[TableAId] FROM {TABLE_B})
Hi Remco,

This is not an answer to your question but consider using:

SELECT {TABLE_A}.*
FROM {TABLE_A}
WHERE
EXISTS (SELECT 1 FROM {TABLE_B} WHERE {TABLE_B}.[TableAId] = {TABLE_A}.[Id])

or NOT EXISTS in the second case.

The performance of this query is better than the one you posted.
Cheers,
André
This solution works like it should.

Thank you André!