I have 2 master objects, A__c and B__c. An object C__c has the 2 reference fields A(child of A__c) and B(child of B__c). There is also another object D__c which has a lookup field(B) to B__c. I need to write a SOQL query something like the below:

Select B__r.Name, B__r.Id, Count(A__r.Id) countA,
(select Max(CreatedDate) from D__c Dobj where Dobj.B__r.Id = Aobj.B__r.Id) from A__c Aobj
group by B__r.Name, B__r.Id

I am new to SOQL and from SQL background, I get one or the other type of errors with this. My query tries to get records from C__c which does not have a master-detail or lookup field to D__c. It has a link to D__c through B__c. Please suggest me how this can be done in SOQL.


Based on your words describing what you want to achieve, you appear to be looking for a Left Anti Join:

    SELECT B__c FROM D__c

However, you actually need to retrieve data rolled up from D__c to B__c, you will need to create a rollup field. Even though you are using a Lookup Relationship, it is still possible using Declarative Lookup Rollup Summaries.

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.