Hi all,

Can you all please help me with the query below.

Select EmployeeID from Company Where CompanyId = 3 and EmployeeID not in (Select EmployeeID2 from CompanyRelation Where CompanyId = 3 and EmployeeID1= 2)

How to write this code in LLBL

I'm having problem with the WHERE clause of the subquery which has an 'and' condition.(code in red color)

Please help,

Thanks...

Possibly the CompanyId filter ? Did you alias it ? Are you using the FieldCompareSetPredicate with it's own filter ?

Select EmployeeID from Company Where CompanyId = 3 and EmployeeID not in (Select EmployeeID2 from CompanyRelation Where CompanyId = 3 and EmployeeID1= 2)

//main filter container:
IRelationPredicateBucket bucket = new RelationPredicateBucket();
                
    //subFilter definition:
    //from CompanyRelation where CompanyId = 3 and EmployeeID1= 2)
    IPredicateExpression subFilter = new PredicateExpression();
    subFilter.Add(new FieldCompareValuePredicate(CompanyRelationFields.CompanyId, null,  ComparisonOperator.Equal, (int)3));
    subFilter.Add(new FieldCompareValuePredicate(CompanyRelationFields.EmployeeID1, null, ComparisonOperator.Equal, (int)2));

//Here is where you apply the subFilter defined above:
//and EmployeeID not in (Select EmployeeID2 from CompanyRelation
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
CompanyFields.EmployeeID, null, CompanyRelationFields.EmployeeID2, null,
SetOperator.In, subFilter,true));
                
//Where CompanyId = 3
bucket.PredicateExpression.Add(new FieldCompareValuePredicate(CompanyFields.CompanyId, null, ComparisonOperator.Equal, (int)3));

goose wrote:

Select EmployeeID from Company Where CompanyId = 3 and EmployeeID not in (Select EmployeeID2 from CompanyRelation Where CompanyId = 3 and EmployeeID1= 2)

//main filter container:
IRelationPredicateBucket bucket = new RelationPredicateBucket();
                
    //subFilter definition:
    //from CompanyRelation where CompanyId = 3 and EmployeeID1= 2)
    IPredicateExpression subFilter = new PredicateExpression();
    subFilter.Add(new FieldCompareValuePredicate(CompanyRelationFields.CompanyId, null,  ComparisonOperator.Equal, (int)3));
    subFilter.Add(new FieldCompareValuePredicate(CompanyRelationFields.EmployeeID1, null, ComparisonOperator.Equal, (int)2));

//Here is where you apply the subFilter defined above:
//and EmployeeID not in (Select EmployeeID2 from CompanyRelation
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
CompanyFields.EmployeeID, null, CompanyRelationFields.EmployeeID2, null,
SetOperator.In, subFilter,true));
                
//Where CompanyId = 3
bucket.PredicateExpression.Add(new FieldCompareValuePredicate(CompanyFields.CompanyId, null, ComparisonOperator.Equal, (int)3));

Thanxs for replying. But how to use 'NOT IN' operator ? My condition is 'Not In' not 'In' ..so can you please tell if t is possible to do in llbl. Because I tried with all other options like SetOperator.NotEqual, SetOperator.NotEqualAny,SetOperator.NotEqualAll .

I'm not getting the desired result.

bucket.PredicateExpression.Add(new FieldCompareSetPredicate( CompanyFields.EmployeeID, null, CompanyRelationFields.EmployeeID2, null, SetOperator.In, subFilter,true));

How to do the SetOperator as 'Not In' ?

Sweety wrote:

goose wrote:

Select EmployeeID from Company Where CompanyId = 3 and EmployeeID not in (Select EmployeeID2 from CompanyRelation Where CompanyId = 3 and EmployeeID1= 2)

//main filter container:
IRelationPredicateBucket bucket = new RelationPredicateBucket();
                
    //subFilter definition:
    //from CompanyRelation where CompanyId = 3 and EmployeeID1= 2)
    IPredicateExpression subFilter = new PredicateExpression();
    subFilter.Add(new FieldCompareValuePredicate(CompanyRelationFields.CompanyId, null,  ComparisonOperator.Equal, (int)3));
    subFilter.Add(new FieldCompareValuePredicate(CompanyRelationFields.EmployeeID1, null, ComparisonOperator.Equal, (int)2));

//Here is where you apply the subFilter defined above:
//and EmployeeID not in (Select EmployeeID2 from CompanyRelation
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(
CompanyFields.EmployeeID, null, CompanyRelationFields.EmployeeID2, null,
SetOperator.In, subFilter,true));
                
//Where CompanyId = 3
bucket.PredicateExpression.Add(new FieldCompareValuePredicate(CompanyFields.CompanyId, null, ComparisonOperator.Equal, (int)3));

Thanxs for replying. But how to use 'NOT IN' operator ? My condition is 'Not In' not 'In' ..so can you please tell if t is possible to do in llbl. Because I tried with all other options like SetOperator.NotEqual, SetOperator.NotEqualAny,SetOperator.NotEqualAll .

I'm not getting the desired result.

bucket.PredicateExpression.Add(new FieldCompareSetPredicate( CompanyFields.EmployeeID, null, CompanyRelationFields.EmployeeID2, null, SetOperator.In, subFilter,true));

How to do the SetOperator as 'Not In' ?

Thank you so much, It is working now, Had to pass 'true' in the 'negate' . Thanks a lot goose