I am attempting to create a query with the objects Opportunity, Courses__c, and Prior_Qualifications__c.

Opportunity has parent-child relationships with both Courses__c and Prior_Qualifications__c. I want to be able to query only information with valid Courses__c records, so that I can disregard any results returned with valid Opportunity object information, but no Courses__c information. Is there a way to do this? Am I able to make a query such that:

Select Id, 
(Select Course_Name__c
from Courses__r)
from Opportunity
where Courses__r.Course_Name__c != ''

I know that this isn't a valid query... but can I do something like this?


What about something like:

    Id IN (SELECT Opportunity__c from Courses__c WHERE Course_Name__c != '')

This will get you all of the opportunities with a valid course.

  • Nice. Can he just query the Courses object with non-blank names and then do group by Opportunity__c field? Just one query instead of two? Aug 26 '13 at 20:02
  • I didn't know you could do an in selector in the where portion of the queries. This makes my life a whole lot simpler... Thanks!
    – josh
    Aug 26 '13 at 21:44
  • 1
    Just take care that your Courses__c table isn't too big. If you query in the where clause has a lot of rows it could hit governor limits are just run slow. Aug 27 '13 at 1:35

Move your WHERE clause into your subquery:

    (SELECT Course_Name__c
        FROM Courses__r
        WHERE Course_Name__c != '')
FROM Opportunity
  • That does work, but it returns Opportunity objects without any Courses__r data.
    – josh
    Aug 26 '13 at 19:56
  • 1
    Yea, you could also do it this way. You would then have to filter through your opportunities however and remove any with a Courses__r.size() == 0 Aug 26 '13 at 19:58

If you don't want to return opportunities with no course, you can query on that object and group to get the ordering correct.

SELECT Opportunity__c, Course_Name__c
FROM Courses__c
WHERE Opportunity__c != null AND Course_Name__c != null
ORDER BY Opportunity__c

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.