1

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!

2
  • An explantation for the downvote would be helpful; at least then I could improve the question. :(
    – risingTide
    Nov 17, 2022 at 21:58
  • 2
    We seem to have someone who just loves going around and downvoting nearly everything :/ I've upvoted to even things out.
    – Derek F
    Nov 17, 2022 at 22:27

1 Answer 1

4

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").

4
  • Nice answer, though there is a question to be asked of the questioner; why must this be done from the Account? You could equally do this from the Opportunity using the account relationship, could you not?
    – Phil W
    Nov 17, 2022 at 22:51
  • Another point about the SOQL RHS being a value not a field; the typical solution is to employ formula fields to perform the comparison required and then to simply test that in the SOQL against TRUE or FALSE. Clearly not specifically part of the question, but you did mention the limitation in SOQL...
    – Phil W
    Nov 17, 2022 at 22:53
  • @Derek F - Thanks for that detailed, helpful answer...and the link to the documentation. Reading from there I found the explanation on how the "join" happens: "where the initial member of the FROM clause in the subquery is related to the initial member of the outer query FROM clause.". And thanks for the justice vote!
    – risingTide
    Nov 17, 2022 at 23:09
  • @PhilW - Yes, you could start at the Opportunity and go in reverse to Account.
    – risingTide
    Nov 18, 2022 at 14:03

You must log in to answer this question.

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