SOQL query help

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

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. enter image description here

So your queries should be like –

SELECT Name, CampaignId, Campaign.Name FROM Opportunity

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

Attribution
Source : Link , Question Author : user28575 , Answer Author : Jarvis

Leave a Comment