I want to bring all the records that are not in an entity

 I want to bring all the records that are not in an entity

  

Is it possible to do a "Not in" or "Not Exists" in an aggregate? Plz help i'm a begginer in Outsystems


You'll have to use a SQL query to do that, instead of an aggregate. Your SQL would look something like this:

SELECT * FROM {YourEntity} WHERE {YourEntity}.[Id] NOT IN (SELECT Id FROM {OtherEntity})

Leidy Navarro wrote:

Is it possible to do a "Not in" or "Not Exists" in an aggregate? Plz help i'm a begginer in Outsystems


Leidy,

You know NOT EXISTS, LEFT JOIN / IS NULL are semantically equivalent, right? So you have this options to use in aggregate. 

But I think in this case it´s more eficiently use Advanced SQL.

You can take a look in this other post

https://www.outsystems.com/forums/discussion/14219/using-the-in-clause-in-an-aggregate/


SyntaxEditor Code Snippet
This is my sql query and no matter what I do I always bring all the records of "Strategy", I only need the records that are not in the entity "StrategiesMeta"

SELECT * FROM {Estrategia} WHERE {Estrategia}.[estado]=1 and {Estrategia}.[Id] NOT IN (SELECT {EstrategiasMetas}.[Id] FROM {EstrategiasMetas} where {EstrategiasMetas}.[MetaNegocioId] =@MetaNegocioId)

Is the "Strategy" id, present in the "StrategiesMeta" entity, really called "Id"? Isn't it called something like "StrategyId"?

Aurelio Santos wrote:

Is the "Strategy" id, present in the "StrategiesMeta" entity, really called "Id"? Isn't it called something like "StrategyId"?

That's right!!! Thank You so much!!!