[SalesForce] Convert SQL query into SOQL query

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'

Best Answer

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.

Related Solutions

[SalesForce] SOQL Contact to Lead Query Help

As a start, the 'Leads__r' relationship is based on a custom field, as noted by the __r at the end, while convertedContactId is a standard field. Off the bat you're querying via the wrong relationship.

Looking at the schema it looks like there's no way to use a subquery (what your join is technically called in SOQL) as there's no relationship name. This is a quirk that can only happen with customer foreign-key (aka lookup) relationships. This means you'll need to change your logic to either

Query leads and their related contacts, like so: SELECT convertedContactId FROM Lead WHERE convertedContactId IN ('your id that isn't provided as text', 'and the other one I'm too lazy to transcribe')
Query the contacts, and then make a second query for any leads that reference them. As you're only querying the Id from the contact the first option makes sense, but I don't know your business case for this.

SOQL is odd in that it doesn't join in the traditional way, and instead uses subqueries in one direction and cross-object fields (e.g. querying lead for convertedContact.name) in the other. (there's also semi-joins and anti-joins, but that's getting longwinded)

[SalesForce] Accounts without Opportunities SOQL Query

I would do the following query :

List<Account> accounts = [SELECT Id, Name FROM Account 
                           WHERE Id NOT IN (SELECT AccountId from Opportunity)];

Updated:

List<TransactionHeader__c> transactions = [SELECT Account__c from TransactionHeader__c limit 10000];
List<Account> accounts = [SELECT Id, Name FROM Account 
                           WHERE Id NOT IN (SELECT Account__c from TransactionHeader__c)];

Debug Log :

09:45:06.025 (25652305)|SOQL_EXECUTE_BEGIN|[1]|Aggregations:0|select Account__c from TransactionHeader__c limit 10000
09:45:06.130 (130013480)|SOQL_EXECUTE_END|[1]|Rows:10000
09:45:06.143 (143338699)|SOQL_EXECUTE_BEGIN|[2]|Aggregations:0|select Id, Name from Account where Id NOT IN (select Account__c from TransactionHeader__c)
09:45:06.356 (356276543)|SOQL_EXECUTE_END|[2]|Rows:2774

Best Answer

Related Solutions

[SalesForce] SOQL Contact to Lead Query Help

[SalesForce] Accounts without Opportunities SOQL Query

Related Topic