Filter aggregate to not include already used items.

Filter aggregate to not include already used items.

  

Hi guys,


Is it possible to filter an aggregate to exclude all items where the ID matches an ID in a different table?

I know how to do this in with an Advanced Query:


Select {object}.*

FROM {object}

Where {object}.[someId] NOT IN (

        SELECT {otherObject}.[Id]

        FROM {otherObject}

)


Is it possible to do this in an Aggregate?

Solution

Hi Tim,

It depends a bit. In your simple example, assuming there's a 1:1 (or n:1) relationship between object.someId and otherObject.Id, you can simply LEFT JOIN ("With or Without" in the Aggregate) otherObject to object, and have a Filter that says "otherObject.Id = NullIdentifier()".

If you have more complex requirements, it might well be impossible, and you have to resort to an SQL Advanced Query.

Solution