8

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?

0

3 Answers 3

15

What about something like:

SELECT
    Id,
    (SELECT
        Id
    FROM
        Courses__r)
FROM
    Opportunity
WHERE
    Id IN (SELECT Opportunity__c from Courses__c WHERE Course_Name__c != '')

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

3
  • 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, 2013 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, 2013 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, 2013 at 1:35
1

Move your WHERE clause into your subquery:

SELECT Id, 
    (SELECT Course_Name__c
        FROM Courses__r
        WHERE Course_Name__c != '')
FROM Opportunity
2
  • That does work, but it returns Opportunity objects without any Courses__r data.
    – josh
    Aug 26, 2013 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, 2013 at 19:58
1

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

You must log in to answer this question.

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