Apex Code Development You need to sign in to do that
Don't have an account?
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?
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?
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
Query can be modified like this to have desired results but not as a data load but can be used in Apex: !~~Mark as a best answer if you feel so and for encouraging~~!
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~~!