function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Sylvie SerpletSylvie Serplet 

SOQL for Parent and Child

Hi all,
I am trying to write a SOQL but could not make it right.
Parent object Policy__c, child object Fact_Finder__c. Lookup relationship between the 2.
What I try to achieve is for all Policy that do not have a Fact Finder (id=null) or one but dated for more than 3 years (Date__c < LAST_N_YEARS:3) update the field on Policy, FactFinderDue = true.
Here is my code: 
global class UpdatePolicywhenFactFinderisDue implements Schedulable { 
    
    global void execute(SchedulableContext ct) { 
        
        List<Policy__c> policylist = [Select Id, Name, Status__c, (Select Id, Date__c From Facts_Finder__r where Date__c < LAST_N_YEARS:3 or Id = null ) From Policy__c where Status__c='Current'];
        
        for (Policy__c po :policylist){
            po.FactFinderDue__c = true;    
        }
        update policylist; 
    }   
}
Thank you in advance for your help.
Sylvie

 
RKSalesforceRKSalesforce
Please change your query as below. Hope this will work. 
List<Policy__c> policylist = [Select Id, Name, Status__c From Policy__c where Status__c='Current' Where Id NOT IN (Select Id, Policy__c, Date__c From Facts_Finder__c where Date__c < LAST_N_YEARS:3]
PLease mark as bwest answer if helped.

Regards,
Ramakant
 
dillip nayak 3dillip nayak 3
List<Policy__c> policylist=[select id,Name,Status__c From Policy__c Where  Id NOT IN (Select policyID__r From Facts_Finder__c) AND Status__c='Current' ]

I don't have idea  policy ID reference name please add policy ID it will be work

Please mark as best answer if helped

Thanks
Sylvie SerpletSylvie Serplet
Thank you both. Second solution is only covering one part of the requirement; when there is no record but not when there is actually a record but which is more than 3 years old. Any other idea?
Alain CabonAlain Cabon
Hello Sylvie,

SOQL has too many constraints so it is impossible with only one request that is the most selective unlike SQL.

You want the accounts without contacts + the accounts having contacts created the last three years (analogy).

SOQL cannot do it with the most  unique selective request and you need two batches with two selective requests or you read almost all the data (one unselective request).
 
  1. Semi join sub-selects are only allowed at the top level WHERE expressions and not in nested WHERE expressions.
  2. Semi join sub-selects are not allowed with the 'OR' operator
  3. Cannot follow the same aggregate relationship twice
These constraints don't exist in SQL and a single query could be created but not in SOQL.