I have two different queries which give the results, however is there a way to combine 2 queries and extend the WHERE clause as below:
Query 1
SELECT Id, Name, (Select ID, Name, Object_C__r.Name from Object_D__r), (Select ID, Object_A__r.Name from Object_B__r WHERE (Object_A__r.ID = 'ABC123' AND End_Date__c >= TODAY) ORDER BY End_Date__c ASC LIMIT 1)
FROM Object_C__c
WHERE ID = 'a0000000000000HY'`
Query 2
SELECT Object_C__c
FROM Object_B__c
WHERE Object_A__r.ID = 'ABC123'
AND End_Date__c >= TODAY
ORDER BY End_Date__c ASC
LIMIT 1
In Query 1, the second inner query made on Object_B has the same WHERE
clause attributes as Query 2's WHERE
clause's attributes.
I need to combine the two queries so that they can become something like this:
SELECT Id, Name, (Select ID, Name, Object_C__r.Name from Object_D__r), (Select ID, Object_A__r.Name from Object_B__r WHERE (Object_A__r.ID = 'ABC123' AND End_Date__c >= TODAY) ORDER BY End_Date__c ASC LIMIT 1) FROM Object_C__c WHERE [SELECT Object_C__c FROM Object_B__c WHERE Object_A__r.ID = 'ABC123' AND End_Date__c >= TODAY ORDER BY End_Date__c ASC LIMIT 1]
Best Answer
Structurally speaking, what you seem to be looking for a semi-join sub-select, which would take a form like this:
You can use this in situations where you need to filter the Ids of the objects in the outer query by the results of an inner query, where the inner query selects exactly one field of type
Id
.Unfortunately, one of the limitations of the semi-join is
Because it looks like you must use these clauses in your subquery, you're not going to be able to use this form in this specific application, but on a more general basis it'd be what you look for to solve this type of problem in one query.
Since your subquery here has a
LIMIT 1
clause attached to it, you really can just run two queries with a simple Apex bind and a bit of logic to check for no results: