1

OK, I am trying to think of the best way of doing this, and all of the solutions seem over complicated... I'm missing something simple and I don't know what. Can someone please help me with a simple solution/best practice for this?

I have two objects that I want to check for conflict. I want to remove all of the items that may have a conflict. This is my code for so far.

    List<junction__c> junc = [SELECT Id, obj2__c 
                             FROM junction__c 
                             WHERE anotherObj__c = :anotherID
                             AND Start_Date_and_Time__c >= : Start
                             AND Start_Date_and_Time__c <= : Finish
                             AND Finish_Date_and_Time__c >= : Start
                             AND Finish_Date_and_Time__c <= : Finish
                             ];

    List<obj2__c> obj2__c [SELECT Id, Name
                          FROM obj2__c
                          WHERE anotherObj__c = : anotherID 
                          ];

How would I return all of the second list (obj2__c) that were not found in the first list?

Thanks!

1
  • 1
    Not 100% sure what you're trying to do, but I maybe look a Left Anti Join query.
    – Andrew L.
    Jun 27 '17 at 23:35
3

A Left Anti Join is indeed probably what you are looking for, if you want to join the two queries together. Assuming the Junction__c.Obj2__c field is a lookup to the Obj2__c object, then it would look like:

List<Obj2__c> records = [
    SELECT Name FROM Obj2__c WHERE Id NOT IN (
        SELECT Obj2__c FROM Junction__c
        WHERE ...
    )
];

If you want to keep the queries separate, you'd need to gather a collection of the Id values and filter no that:

Set<Id> obj2Ids = new Set<Id>();
for (Junction__c record : junc)
{
    obj2Ids.add(record.Obj2__c);
}
List<Obj2__c> records = [
    SELECT Name FROM Obj2__c WHERE Id NOT IN :obj2Ids
];
0

Your question is not entirely clear on the relationships of different objects, however, if you don't want the records which were retrieved in the first query, can you not do something like this:

List<obj2__c> obj2__c [SELECT Id, Name
                      FROM obj2__c
                      WHERE anotherObj__c != : anotherID 
                      ];

Also, if you have a direct relationship or list available which should not be retrieved in the second result, use something like this:

List<obj2__c> obj2__c [SELECT Id, Name
                          FROM obj2__c
                          WHERE anotherObj__c NOT IN : listOfRecords
                          ];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.