Soql query on parents other child object

Question

I want to write a soql query , My scenario is as follows. I have three objects objectA,objectB,objectC. objectA is the parent object for both objectB,objectC.

• objectA - objectB (Master-detail relationship)
• objectA - objectC (Master-detail relationship)`

ObjectB has two fields:

• Start_date__c
• End_date__c

ObjectC has a field:

• Date__c

Now i want to retrieve records with date__c in objectC those were in between start_date__c and end_date__c of objectB. how can i achieve such functionality .

Answer 1

You can try to query the junction object like this:

Select ObjectC__r.Id, ObjectC__r.Name
From ObjectA__c
Where ObjectC__r.Date__c >= ObjectB__r.start_date__c
And ObjectC__r.Date__c <= ObjectB__r.end_date__c