How to perform a JOIN in Salesforce (SOQL) - Simple Salesforce (Python Library)

Question

I am using simpleSalesforce library for python to query SalesForce.

I am looking at two different object in SalesForce: Account and Opportunity (parent-child). There is an accountId inside the opportunity object.

I am trying to perform an inner join between the two and select the results (fields from both objects). a normal SQL statement would look like this:

SELECT acc.Name, opp.StageName
FROM Account AS acc
JOIN Opportunity AS opp ON acc.Id = opp.AccountId

I am not sure how to translate this kind of query into SOQL.

Mesmerism

Answer 1

Salesforce doesn't allow arbitrary joins. You must write relationship queries to traverse predefined relationships in the Salesforce schema.

Here, you'd do something like

SELECT Name, (SELECT StageName FROM Opportunities) 
FROM Account

No explicit join logic is required, or indeed permitted. Note too that your return values will be structured, nested JSON objects - Salesforce does not return flat rows like a SQL query would.

Blighter

Why did you use the plural term and how did you know you had to do that? Opportunity -> Opportunities – Mesmerism

It's a parent-child relationship query; that's how those all look in SOQL. You can find the child relationship name in Apex or in the Salesforce UI. – Blighter

Answer 2

A couple notes about SOQL:

• No aliases allowed
• No explicit joins allowed

But with that in mind, it's still possible to get the outcome you want by directly using the desired fields names as an "attribute" of the object relationship. Example:

SELECT account.Name, Name, StageName FROM Opportunity

Which will grab the related account name, the opportunity name, and the opportunity stage name in one query.

As long as the field on your base object is of a type Lookup or Master-Detail, you can use this type of relationship. In the case of custom fields, you switch the __c over to __r though.

Example: Opportunity has a relationship to custom object Address__c and we want to know what city & country these opportunities are in:

SELECT Address__r.Country__c, Address__r.City__c,Name, StageName from Opportunity

Prayer