Salesforce objects cannot be joined using SOQL like tables in traditional databases, unfortunately. More context and detail here and here.

SOQL really only supports selecting data from multiple objects if there is an explicit lookup or master-detail relationship between them.

from simple_salesforce import Salesforce
import pandas as pd

Credentials below are for a trailhead org created for purposes of this note, only. It contains only default data provided by Salesforce.

sf = Salesforce(username='[email protected]',
                password='password_201019',
                security_token='ozSA9fb0lwwyqslXtUDxRDQu')

Examples Joins

These examples are taken from this link.

There are two approaches demonstrated below. The first involves merging the tables using Pandas. The seconds attempts a JOIN using SOQL, which has no native JOIN keyword.

Example JOINs Using Pandas

Each object is queried into its own Pandas DataFrame and then joined using Panda’s merge function.

Child to Parent Query

Answers the question, “What is the name of our Accounts and which user owns them?”

query = '''
SELECT Id, Name, OwnerId
  FROM Account
'''
records = sf.query_all(query)
account = (pd.DataFrame(records['records'])
          .drop(columns=['attributes']))
account.head(3)

query = '''
SELECT Id, Name
  FROM User
'''
records = sf.query_all(query)
user = (pd.DataFrame(records['records'])
        .drop(columns=['attributes']))
user.columns = 'Owner' + user.columns
user.head(3)

result = pd.merge(account, user,
                  on='OwnerId',
                  how='left',
                  indicator=True)
print(result._merge.value_counts())
result.head(3)

both          12
right_only     0
left_only      0
Name: _merge, dtype: int64

Parent to Child Query

Answers the question, “Who are the contacts for each Account?”

query = '''
SELECT Id, Name
  FROM Account
'''
records = sf.query_all(query)
account = (pd.DataFrame(records['records'])
          .drop(columns=['attributes']))
account.columns = 'Account' + account.columns
account.head(3)

query = '''
SELECT Id, Name, AccountId
  FROM Contact
'''
records = sf.query_all(query)
contact = (pd.DataFrame(records['records'])
        .drop(columns=['attributes']))
contact.head(3)

result = pd.merge(account, contact,
                  on='AccountId',
                  how='left',
                  indicator=True)
print(result._merge.value_counts())
result.head(3)

both          20
right_only     0
left_only      0
Name: _merge, dtype: int64

Query with WHERE and Combinational Logic

query = '''
SELECT Id, Name
  FROM Account
 WHERE Name = 'Express Logistics and Transport'
    OR Name = 'University of Arizona'
'''
records = sf.query_all(query)
account = (pd.DataFrame(records['records'])
          .drop(columns=['attributes']))
account.columns = 'Account' + account.columns
account.head(3)

result = pd.merge(account, contact,
                  on='AccountId',
                  how='left',
                  indicator=True)
print(result._merge.value_counts())
result.head(3)

both          3
right_only    0
left_only     0
Name: _merge, dtype: int64

Example JOINs Using SOQL

Child to Parent Query

Answers the question, “What is the name of our Accounts and which user owns them?”

One-to-One relationship: for each Account, there will be at most one Owner with one Name.

• You can only go one level down in relationships when querying from parent to child.
• You can go five levels up the relationship chain when querying from a child to a parent.

query = '''
SELECT Name, Owner.Name
  FROM Account
'''
records = sf.query_all(query)
result = (pd.DataFrame(records['records'])
          .drop(columns=['attributes']))
result.head(3)

Parsing the data with Pandas isn’t as clean as it is when querying a single object. The query results in a nested dictionary that would require additional work to parse the Owner Name out. See below.

records['records'][0]

OrderedDict([('attributes',
              OrderedDict([('type', 'Account'),
                           ('url',
                            '/services/data/v42.0/sobjects/Account/0014W000026oyWmQAI')])),
             ('Name', 'Express Logistics and Transport'),
             ('Owner',
              OrderedDict([('attributes',
                            OrderedDict([('type', 'User'),
                                         ('url',
                                          '/services/data/v42.0/sobjects/User/0054W00000CKU8EQAX')])),
                           ('Name', 'Ryan Wingate')]))])

Parent to Child Query

Answers the question, “Who are the contacts for each Account?”

One-to-Many relationship: for each Account, there can be multiple Contacts.

query = '''
SELECT Account.Name,
       (SELECT Contact.Name FROM contacts)
  FROM Account
'''
records = sf.query_all(query)
result = (pd.DataFrame(records['records'])
          .drop(columns=['attributes']))
result.head(3)