4

Is this possible to use one subquery to query for two child records, one the latest from the past and the earliest from the future? If so, how to do it?

If I try to run this query

select id, name, 
(select Id, Name, DateTimeField__c from Children__r WHERE DateTimeField__c <= TODAY ORDER BY DateTimeField__c DESC LIMIT 1 ) ,
(select Id, Name, DateTimeField__c from Children__r WHERE DateTimeField__c >= TODAY ORDER BY DateTimeField__c ASC LIMIT 1 ) 
from Parent__c where id in ( select Parend__c from Child__c )

I receive error

Cannot follow the same aggregate relationship twice: Children__r

Improve this question

1 Answer 1

Reset to default
3

You have to either query all child records, loop through them to find the ones you want, or you have to use two queries. There's no syntax that will get you exactly those two records without any waste. Either you'll need more database queries than just one, or you'll return more rows than you need.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .