How to query records having at least one valid child record?

Question

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?

Answer 1

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.

Answer 2

Move your WHERE clause into your subquery:

SELECT Id, 
    (SELECT Course_Name__c
        FROM Courses__r
        WHERE Course_Name__c != '')
FROM Opportunity

Answer 3

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