Aggregation based on Junction object in a subquery

Question

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.

Answer 1

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

SELECT ... FROM A__c
WHERE B__c NOT IN (
    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.