Search code examples
salesforcesoql

How to perform a LEFT JOIN in SOQL

I have a query in SQL that I want to convert to SOQL. I know that a LEFT JOIN is not possible in SOQL. But I don't how to write this in SOQL.

I want to check Cases without Decision__c. There is a Lookup relation between Case(Id) and Decision__c (Case__c).

That would be in SQL:

Select Id FROM Case LEFT JOIN Decision__c D on D.Case__c = Case.Id WHERE Case__c IS NULL

I exported all Cases (Case) and all Decisions (Decision__c) to Excel. With a VLOOKLUP I connected the Case with the decision. An error = no linked decision.

I exported the objects in PowerQuery and performed a left join to merge the two queries. Those with no decision where easily filtered (null value).

So I got my list of Cases without Decision, but I want to know if I can get this list with a SOQL query, instead of these extra steps.

Solution

  • To simply put it, you must, literally, select cases without Decision__c, the query should look like this:

    SELECT Id FROM Case WHERE Id NOT IN(SELECT Case__c FROM Decision__c)

    Although we don't JOINs in Salesforce we can use several "subqueries" to help filter records.

    refer to the following link: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select.htm