Saleforce retrieving fields from two different objects – (SOQL) Simple Salesforce Python

I am using simpleSalesforce library for python to query SalesForce.

I am looking at two different object in SalesForce: Account and Backend (parent-child). The Id in account matches the records of Backend through acc_id

I am trying to do this:

sf.query_all("SELECT AccEmail__c, (select custid from Backend__c) from Account where Id in (select acc_id from Backend__c where custid LIKE '%CUST%')")

But I get the response showing:

Malformed request – didn’t understand the relationship – in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the ‘__r’ after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.”, ‘errorCode’: ‘INVALID_TYPE’}]

What am I doing wrong how can I fix this?

Answer

Read up about relationships

• https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_understanding.htm
• https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships_and_custom_objects.htm

Most likely your query has to be something like

SELECT AccEmail__c,
    (select custid__c from Backends__r)
from Account 
where Id in (select account__c from Backend__c where custid__c LIKE '%CUST%')

or even

SELECT AccEmail__c,
    (select custid__c from Backends__r where custid__c LIKE '%CUST%')
from Account 
where Id in (select account__c from Backend__c where custid__c LIKE '%CUST%')

Or if you want it flat

SELECT CustId__c, Account__r.AccEmail__c
FROM Backend__c
WHERE CustId__c LIKE '%CUST%'