SOQL Syntax to Return Opportunity Data via Account Object

Question

I'm trying to write SOQL to access fields from both the Account and Opportunity objects from the Account object. Specifically I'm trying to retrieve the Id and Name fields from Account while also returning the Amount field from Opportunity. I'm trying to do this based on the relationship between the two objects for a specific account owner.

Here's the syntax I have so far, although it is obviously not correct:

SELECT Id, Name, Opportunity__r.Amount 
FROM Account 
WHERE OwnerId = '0053l000X09r9TcAAI'
AND Opportunity__r.AccountId = Id

Any help would be greatly appreciated; thanks!

Answer 1

SOQL doesn't support arbitrary joins like most other SQL dialects do, and we can't compare a field to another field (more accurately: the RHS of a filter expression cannot be an SObject field).

What SOQL does allow us to do, though, is get at child records using a subquery. The relevant documentation here is Relationship Queries.

SELECT 
    Id, Name,
    (SELECT Id, Amount FROM Opportunities)
FROM Account 
WHERE OwnerId = '0053l000X09r9TcAAI'

A parent-child subquery (in the SELECT clause) uses the child relationship name. This is typically the plural of the SObject name. If it's a custom relationship (master-detail, or lookup), i.e. if you created the relationship field yourself rather than it being a standard field provided by Salesforce, then you'd need to add __r to it.

A parent-child subquery gets you an embedded List<SObject> in the overall result, accessed using the child relationship name.

for(Account acct :[SELECT Id, Name, (SELECT Id, Amount FROM Opportunities) FROM Account WHERE OwnerId = '0053l000X09r9TcAAI']){
    for(Opportunity opp :acct.Opportunities){
        system.debug(acct.Id);
        system.debug(opp.Amount);
    }
}

The nested loop approach is the safest way to deal with subquery results. You could access acct.Opportunities directly, without a loop, and treat it like any other List, but you'll get an error if there are too many (>= ~200) child records (when Salesforce attempts to make an internal call to "queryMore").