I have a requirement to fetching data from Sales force. I need to get the data from two custom objects. I have written query in sql can anyone help me to convert it into SOQL
SELECT ID, Name, Crop_Year__c, Targeted_Enrollment_Segments__c, Description__c, Start_Date__c,
End_Date__c from Enrollment_Program__c EP
Left Join Account_Enrollment__c AE on EP.Crop_Year__c = AE.Crop_Year__c and EP.ID =
AE.Enrollment_Program__c
where AE.Account__c = 'xyz'
As you probably know, Salesforce SOQL doesn't have explicit JOIN clauses. It does that for you implicitly based on related object fields. That means you'll have to query Account_Enrollment__c and traverse the fields to get the related Enrollment_Program__c Lookup relationship.
Another problem is Salesforce only performs joins based on primary and foreign keys, so the EP.Crop_Year__c = AE.Crop_Year__c in your query won't work.
So, with that said, you can try this:
SELECT Enrollment_Program__c, Enrollment_Program__e.Name,
Enrollment_Program__r.Crop_Year__c, Enrollment_Program__r.Targeted_Enrollment_Segments__c,
Enrollment_Program__r.Description__c, Enrollment_Program__r.Start_Date__c,
Enrollment_Program__r.End_Date__c
FROM Account_Entrollment_Program__c WHERE Account__c = 'zyz'
If you know beforehand what the Crop_Year__c value is, you can just add this to your query:
AND Crop_Year__c=:year AND Enrollment_Program__c.Crop_Year__c=:year
Some details on the queries:
The __r suffix is how you get the lookup object addressed in the query. If you are interested only in the id, you can use __c.
The :year is how you pass the parameter year to the query. If you want to append it as text you can just use ... Crop_Year='+ year + '.