I was trying something like along those lines but it's not working:

              SELECT Id
              FROM Orders
              WHERE Custom_Field__c = 'SomeValue'
   FROM Opportunity
   WHERE Somefield__c = :listOfSomeField
       (StageName = 'Committed'
       Orders.size() > 0)

Is there a different way to do this within the query?


You would need to do a sub-query filter:

FROM Opportunity
   WHERE Somefield__c = :listOfSomeField
   AND Id IN (SELECT OpportunityId FROM Order WHERE Custom_Field__c = 'SomeValue')

However, please note that you cannot combine this method with "OR", so you may actually need two different queries after all, or you'll have to do some post-processing in Apex Code to determine if the criteria was met.

| improve this answer | |
  • Thank you, that's what I was wondering about. So I guess I won't be able to resolve it within the WHERE clause at this point. Thanks again! – Arthlete May 15 '18 at 17:18

If you just care about zero or non-zero, you can use Left Anti Join or Left Outer Join.

SELECT ... FROM Opportunity WHERE Id IN (SELECT OpportunityId FROM Order)
| improve this answer | |

You can actually achieve this through aliasing a relationship field, although you have to use the sub-object as your primary object. Using your example, you could do the following to get the Ids of all opportunities that have more than 0 orders:

SELECT opp.Id 
FROM Order ord, Order.Opportunity__r opp
HAVING opp.Somefield__c IN :listOfSomeField
  AND COUNT(ord.Id) > 0

Just remember that the return datatype would be a list of AggregateResult.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.