function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Mildred MarreroMildred Marrero 

SOQL Join Three Objects

I am trying to build a SOQL query with the Opportunity, OpportunityContactRoles and Contact Objects. The Opportunity Contact Roles object does not have the AccountID (nor does Salesforce.com allows you to create a custom field to this table) therefore in order for your query to have the Account Name of those contacts you have under your contact roles you must link the contact table to the OpportunityContactRoles. In our case we can select contacts in our OpportunityContactRoles that ARE NOT a contact of the Account link to the Opportunity such as a partner or a consultant.

The purpose of this query is to create Consultant Bios from those consultants participating in our Campaigns. In the Bios we would like to list the opportunities of the consultants account regardless of who they are sending to participate in our campaigns. 

The data would look like this.

Campaign Particpant
Consultant Account: ABC
Consultant Campaign Member:  John Doe

List of Opportunties based on where opportunitycontactroles AccountID = Campaign Member AccountID
Opportunity            Account
02100                    Candyland
02101                    Frosties

This is the code that I have at the moment and whenever I try to add the contact table I get an error. 

SELECT RecordTypeDesc__c, Id, Name, Opportunity_Number__c, AccountId, Account_Name__c, 
(SELECT ContactId,OpportunityId,Role FROM OpportunityContactRoles) 
FROM Opportunity
WHERE RecordTypeDesc__c = 'Project'



 
David ZhuDavid Zhu
if taking out all custom fields, the SOQL runs without any error. You may need to check all custom fields in your soql.

SELECT  Id, Name,  AccountId, 
(SELECT ContactId,OpportunityId,Role FROM OpportunityContactRoles) 
FROM Opportunity


 
James LoghryJames Loghry

Mildred,

What does your SOQL with the Contact table look like?  The OpportunityContactRoles is kind of a standard junction object, so you could bring in the Contact information like so (I tested this and it worked on my developer org):

SELECT Id, 
(SELECT Contact.Name,Contact.Email,Contact.Id,ContactId,Role FROM OpportunityContactRoles) 
FROM Opportunity
Mildred MarreroMildred Marrero
Thank you both for the reply. Unfortunately I need the AccountID for the contacts selected in the OpportunityContactRoles which is different from the accountId of the Opportunity. Both of the codes above work but I still don't know if the account of the the contact role is the same as the opportunity or a different. :-(
David ZhuDavid Zhu
list<Opportunity> opps = [SELECT  Id, Name, AccountId, (SELECT id,ContactId,contact.account.name,contact.account.id FROM OpportunityContactRoles) FROM Opportunity];
list<id> accids = new list<id>();

for (Opportunity opp : opps)
{
	for (OpportunityContactRole ocr : opp.OpportunityContactRoles)
	{
		accids.add(ocr.contact.account.id);		
	}
    
    system.debug(accids);
}

variable accids would get your the account ids of all contact roles. 
 
Rob Easton 1Rob Easton 1
I'm late to the party but I like David's response and ran it in Workbench
SELECT  Id, Name, AccountId,
(SELECT id,ContactId,contact.account.name,contact.account.id FROM OpportunityContactRoles)
FROM Opportunity
and it looks like it's on target with Mildred's request