I need to get all the contact information of my partners. However, I could not just create a report to capture all the contact information of different partners. I am able to create reports individually in the objects in partner, account, and contact, and the junction object amoung three reports is the partner account name. Each partner account has multiple contacts. I am trying to create a SOQL query to capture all the partners, contacts, and accounts, but not sure if the account is the parent of the partner object? Can anyone give me an idea for this query?

SELECT id, Name
  (SELECT ID, StartDate, EndDate, Reseller_Partner_Type__c,title,email, firstname, lastname
     FROM  Contacts
   (SELECT id,OwnerId, Start_Date__c, Name, CreatedDate, Internal_Status__c, industry, partner_Account_ID__r.Name 
     FROM Partners
       WHERE partner_account_name_c='xxx"
FROM Account 
   WHERE Name IN
      (SELECT  partner_Account_ID__c 
     FROM Partners_c
        WHERE partnername IN = 'xxx'
| improve this question | | | | |

You need to get clarity on whether there is or is not a relationship (lookup/master-detail) with the Partners__c object. You can't write a query without knowing your data model. You can review the objects in your Setup interface or use Schema Builder, and for the purpose of writing subqueries, make sure to look at the definition of any relationship fields (which are located on the child object) to get the relationship name.

As you've found, there's two ways you can associate your Accounts with your Partners in the context of this query. Both assume there is a lookup relationship (partner_Account_ID__c) between Partners__c and Account. However, your query is confused on a couple of points.

Your SELECT .. FROM Partners subquery is the route by which you would pull information about the Partner associated with each Account in your query results. The relationship name (Partners, here) is defined on the lookup field, although Partners is not a legal name for a custom relationship. The WHERE clause on this subquery does not make sense, because a subquery like this one is already limited based upon the lookup relationship between the objects (i.e., no filtration by matching names is required). For more information, read through Relationship Queries

The other tool you use a semi-join, which you do in the WHERE portion of your query. A semi-join can limit query results based on the results of another query against a related object, like your query against Partners__c. However, your sub-select does not make sense. You appear to be matching Account.Name against a field (partner_Account_ID__c) that contains Id values, so you should do WHERE Id IN. Additionally, partnername is not a legal custom field name.

Based on your statement of your objectives, it sounds like you probably do need to perform a semi-join, but you may not need the relationship subquery. You'd need to be more specific about exactly which data points you need to obtain.

Lastly, please note that 'junction object' has a specific meaning in Salesforce; a junction object is the detail on two master-detail relationships to different objects, representing a many-to-many relationship.

If you're struggling with SOQL, I recommend reading through the entire SOQL reference and the Write SOQL Queries unit on Trailhead. But the first step is getting a clear understanding of your own data model - you simply cannot form queries if you don't have this.

| improve this answer | | | | |

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.