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?

14

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.

| improve this answer | |
  • 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? – Sergej Utko 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. – Ralph Callaway Aug 27 '13 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
| improve this answer | |
  • 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 – Jesse Altman Aug 26 '13 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
| 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.