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