0

Currently trying to do an INNER JOIN on my Account and Opportunity tables and am running into the following error. I am using Restforce (SFDC maintained Ruby SDK) to run these queries against a client.

Restforce::ErrorCode::MalformedQuery: MALFORMED_QUERY: 
SELECT Id FROM Account INNER JOIN Opportunity ON AccountId =
                             ^
ERROR at Row:1:Column:30
unexpected token: 'JOIN'

The query I am trying to run is as follows:

SELECT Id FROM Account INNER JOIN Opportunity ON AccountId = Account.id WHERE Opportunity.Owner.Email = 'example.com' OR Account.Named_ADR__r.Email = 'example.com' OR Account.Owner.Email = 'example.com'

Not very familiar with SOQL but am struggling to understand how to do this in Salesforce land. Can anybody lend a hand?

2 Answers 2

1

There used to be much better documentation on how joins work in SOQL, but I haven't found it since the massive migration that happened a while back.

It seems like what you want here is to include child records in your query, which would work like so:

SELECT
    Id, (SELECT Id FROM Opportunities WHERE Owner.Email = '...')
FROM Account
1
  • I was worried I was looking in the wrong spots. I guess I'm not the only one that feels the documentation with joins is somewhat lacking - especially for newbies. Really appreciate the answer. Looks like it's working as I wish. Dec 7, 2020 at 19:43
2

You should refer to SOQL documentation. Salesforce doesn't allow joins, but allows relationship queries using object driven model. There are ways to retrieve upstream (parents) and downstream records (child) via SOQL queries

Parent:

select id, Account.Name from Contact

Child:

select id, (select id, Firstname from Contacts) from Account

Suggested read: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_query_using.htm

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.