[SalesForce] Accounts without Opportunities SOQL Query

I have a requirement to query for master objects that have no corresponding detail objects. This would be similar to querying for Accounts with no Opportunities.

In SQL I might do this:

SELECT Id FROM Account A LEFT JOIN Opportunity O ON O.AccountID = A.Id WHERE O.AccountId IS NULL.

What is the SOQL way of doing this?

Best Answer

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

Related Solutions

[SalesForce] SOQL question – Select Opportunities based on Contact Fields

The idea here is that you are first selecting all Contacts with FirstName of Jane here SELECT OpportunityId from Contact WHERE FirstName = 'Jane' and pulling the OpportunityId for each of those records... You then feed that into a typical Opportunity query where the Opportunity.AccountId = the Contact.AccountId list you just created with the sub-query above...

SELECT
    Id
FROM
    Opportunity
WHERE
    AccountId IN (SELECT AccountId from Contact WHERE FirstName = 'Jane')

[SalesForce] Dynamically create SOQL query from sobject

You need to build a dynamic SOQL query using the object metadata on the object itself.

Using these methods you can get and set methods on an object dynamically as well as get the metadata objects. You can loop through the field metadata to get field names and test the object to see if they are not null. If not, include them in the query.

The general idea is to build the query by selecting the fields you want from the object, then dynamically building the WHERE. Loop through the fields on the object and call Object value = anAccount.get(fieldName) to see the contents. If not null, add the field to the where: 'AND ' + fieldName + ' = \'' + value + '\''

For a more robust example, see the describeSObjects() documentation.

Best Answer

Related Solutions

[SalesForce] SOQL question – Select Opportunities based on Contact Fields

[SalesForce] Dynamically create SOQL query from sobject

Related Topic