0

I need to use a subquery in a the HAVING statement so that we neglect the parent account if it does not have any child account. As follows the SOQL query that I have right now:

SELECT Name, (SELECT Id FROM Accounts__r)
FROM Account
WHERE Type = 'Business Partner'
HAVING (SELECT Id FROM Accounts__r) > 0

How can I achieve this?

2
  • 1
    if its master detail relationship means why cant you create roll up summary and you can check that condition,
    – Annappa PH
    Commented Aug 21, 2018 at 9:53
  • Hi @AnnappaPH thank you for your comment, unfortunately, it is not possible to create a roll up summary field from parent account and child accounts. Commented Aug 21, 2018 at 10:31

3 Answers 3

5

We have Salesforce limitation that we can't use same object twice in SOQL

The inner and outer selects should not be on the same object type

You can achieve this by using two SOQL

List<Account> lstChilds = [Select Parent_Account__c From Account Where Parent_Account__c  != ''];
Set<Id> setParentAcIds = new Set<Id>();
for(Account acc : lstChildIds){
     setParentAcIds.Add(acc.Parent_Account__c );
}
List<Account> lstAccHavingChild = [Select Id, Name From Account Where Id in : setParentAcIds];
0
1

According to the developer documentation can't use semi-joins or relationship queries with a HAVING clause:

A HAVING clause can't contain any semi- or anti-joins. A semi-join is a subquery on another object in an IN clause to restrict the records returned. An anti-join is a subquery on another object in a NOT IN clause to restrict the records returned.

You'll have to store the count directly on each record and filter using a WHERE clause. If you have a master-detail relationship you should be able to create a roll-up summary and filter on that. If it's just a regular lookup you could use a flow or trigger (or the excellent declarative rollups tool) to store the count directly on the contact.

1

For this sort of application, you should use WHERE IN. However, per Sunil, whereas we can do this:

SELECT Name, (SELECT Id, Name FROM Contacts)
FROM Account
WHERE Id IN (SELECT AccountId FROM Contact)
LIMIT 10

We cannot do it with self-referencing keys, for instance:

SELECT Name, (SELECT Id FROM ChildAccounts)
FROM Account
WHERE Id IN (SELECT ParentId FROM Account)
LIMIT 10

Returns the error:

The inner and outer selects should not be on the same object type

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .