[SalesForce] Extending WHERE clause with condition from another query

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:

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 IN (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)

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

COUNT, FOR UPDATE, ORDER BY, and LIMIT are not supported in subqueries.

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:

List<Object_B__c> resultsFirst = [SELECT ... FROM Object_B__c ... LIMIT 1];
List<Object_C__c> resultsSecond;     
if (resultsFirst.size() == 1) {
    resultsSecond = [SELECT ... FROM Object_C__c WHERE Id = :resultsFirst[0].Id];
}
Related Topic