function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Jean Grey 10Jean Grey 10 

Nested SOQL query to get both campaign and opportunity data from contact records

I am trying to build a query to help with buyer-journey reporting. I am trying to do this with only SOQL, not Apex. I think I might be querying the wrong object but I'm not sure how to build this query to give me everything I need.

Here is my query: 

SELECT Id,FirstRespondedDate,Campaign.Name,Status,Date_Responded__c,CreatedDate,ContactId FROM CampaignMember WHERE CampaignId IN (SELECT CampaignId FROM Opportunity WHERE StageName = 'Closed–Won' AND CloseDate = THIS_YEAR) AND ContactId IN (SELECT ContactId FROM OpportunityContactRole)

This works and gives me almost everything I need, but I need to return CloseDate in addition to these other fields. Is this possible? Do I need to flip this and query the Contact object instead, using nested queries to grab both campaign member and opportunity fields?
Best Answer chosen by Jean Grey 10
Prince_sfdcPrince_sfdc

Hi Jean,

You may use like following. 

List<campaign> campList = new List<campaign>(); will store the query result.

for(campaign camp : campList){
	for(Opportunity opp: camp.Opportunities){
		//some Logic or to capture values.
	}
	for(CampaignMember cMem : camp.CampaignMembers){
		//some Logic or to capture values.
	}
}
!~~Mark as a best answer if you feel so and for encouraging~~!

All Answers

Prince_sfdcPrince_sfdc
Hi Jean,

Query can be modified like this to have desired results but not as a data load but can be used in Apex: 
select id, name, (select id,FirstRespondedDate,Status,CreatedDate,ContactId from CampaignMembers where ContactId IN (SELECT ContactId FROM OpportunityContactRole)),(select id,name,closeDate from Opportunities) from campaign where Id in (SELECT CampaignId FROM Opportunity WHERE StageName = 'Closed Won' AND CloseDate = THIS_YEAR)
!~~Mark as a best answer if you feel so and for encouraging~~!
 
Jean Grey 10Jean Grey 10
Can you explain how you would use this in Apex?
Prince_sfdcPrince_sfdc

Hi Jean,

You may use like following. 

List<campaign> campList = new List<campaign>(); will store the query result.

for(campaign camp : campList){
	for(Opportunity opp: camp.Opportunities){
		//some Logic or to capture values.
	}
	for(CampaignMember cMem : camp.CampaignMembers){
		//some Logic or to capture values.
	}
}
!~~Mark as a best answer if you feel so and for encouraging~~!
This was selected as the best answer