SOQL query help

Question

I am pretty new to Salesforce and would appreciate any help on this.

This is my data model

Opportunities : {O1, O2, O3}

Campaigns : {C1,C2}

Campaign Members : {C1M1 , C2M1,C2M2}

Campaign Members have a type field called "Type__c" with possible picklist values as {"Red", "Green", "Yellow"}

C1M1 is of type "Red"

Expected output :

O1 : C1 : C1M1

They want me to pull all opp/camp/camp mem details where campaign member is "Red"

In my previous CRM, it was quite easy to accomplish using standard SQL like below

Opportunity.CampaignId = Campaign.Id
and
CampaignMember.CampaignId = Campaign.Id
and
CampaignMember.Type = "Red"

I tried various SOQL queries in "Query Editor" in Developer console and almost all of them given me "Unknown error" or "Malformed Query".

Can someone let me know whether it is possible to write a SOQL query that does the above ?

Answer 1

If you want to find out the relationships between objects you can go to Schema Builder. Setup> Schema Builder> then select the object you want the details, then click on auto layout.

The many side of a relationship actually connects to the field that represents a relationship.

So your queries should be like -

SELECT Name, CampaignId, Campaign.Name FROM Opportunity

select campaignId, campaign.name from CampaignMember where status__c = 'Red'

Answer 2

SOQL is not full feature query langue like SQL. So you will not be able to achieve it in SOQL as exactly the same way of SQL.

So, you have 3 options here:

1) Split your queries in two queries and fetch the Campaigns where status__c = 'Red' And then based on those ids pull opportunity details.

2) Write query like following and then filter unwanted data in for loop.

Select id , name, (select id , amount from Opportunities), (select id, status from CampaignMembers ) from Campaign 

3) Create rollup summery field on campaign that count the all campaign member where status__c = 'Red' and then query something like:

SELECT Name, CampaignId, Campaign.count_of_status__c   FROM Opportunity where Campaign.count_of_status__c > 0

There are verity of factors which will decide which approch will fit best to your needs for ex. the data returned by both of above queries, performance requirements etc.

This link will help you to understand how to write relationship queries.

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships.htm