5

I've been trying to figure out how to turn this SQL query into a SOQL query. Every time I think I am getting better with SOQL something comes along and erases that thought. Account is the parent object and has a Master-Detail relationship with Merchant Location. Any help is appreciated. Yes I have studied all of the Salesforce documentation on SOQL and it has been helpful in my learning. I can't seem to get my mind wrapped around this query.

select mo.ISOOffice__Merchant_Location__c as Main_Location_ID from Account a 

left outer join ISOOffice__Merchant_Opportunity__c mo on a.Id = mo.ISOOffice__Account__c 
where a.name = 'Company Name' 

and mo.ISOOffice__Opportunity_Type__c = 'New Merchant'
7

Also you can directly query in child object using relationship fields,

[select ISOOffice__Merchant_Location__c,Account__r.Name 
 from ISOOffice__Merchant_Opportunity__c where 
 ISOOffice__Opportunity_Type__c = 'New Merchant' AND 
 Account__r.Name = 'Goodwill of Southwestern Pennsylvania']

You need to prefix ISOOffice__Merchant_Location__c with Account__r, if this field is in account.

3

Not sure I fully follow your data model, but a parent-child relationship query would look like this:

Account a = [
        select (
                select ISOOffice__Merchant_Location__c
                from ISOOffice__Merchant_Opportunities__r
                where ISOOffice__Opportunity_Type__c = 'New Merchant'
                )
        from Account
        where Name = 'Goodwill of Southwestern Pennsylvania'
        ];
for (ISOOffice__Merchant_Opportunity__c o : a.ISOOffice__Merchant_Opportunities__r) {
    String mainLocationId = o.ISOOffice__Merchant_Location__c;
    ...
}

assuming the conventional plural has been used for the relationship name.

  • The problem I am having is there are multiple Locations per Account. I need to grab the Location that has the OpportunityType of 'New Merchant' WHERE the Name = 'Goodwill of Southwestern Pennsylvania' – twiggler Jun 11 '15 at 18:17
  • @twiggler That filter is included so if the code gets to the ... there is one (or more) of that type. Consider Victor's solution too; if you don't need any Account fields it is more direct. (Basing the query on the child object gives you no data at all if there are no children.) – Keith C Jun 11 '15 at 19:09
  • hey guys thank you both! I was able to figure out what I needed and learn some new ways of thinking. Victor's solution gave me what I needed but Keith you showed me a different approach. very much appreciated – twiggler Jun 11 '15 at 19:56
  • 1
    @twiggler, coming from a database background, one thing that helped me years ago transitioning to Salesforce was to stop thinking about how I would previously join tables and instead start thinking consciously about all records as parent and child. That also helped me come to terms with the concept of getting back collections of objects in parent-child groupings rather than joined result set rows all mashed together. – Mark Pond Jun 11 '15 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.