0

I have a requirement to find total number of parent records which are not associated to any child records. Now these two parent and child records are from single object called Project__c.

To achieve this, I have tried the below query:

SELECT Id, Name FROM Project__c WHERE Id NOT IN (SELECT Parent_Project__c FROM Project__c)

I am getting the error as

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

can anyone please let me know how to resolve this issue. Thanks!

4
  • 1
    The query you have do not make a lot of sense. What are you trying to achieve? May 26, 2021 at 12:46
  • @MohithShrivastava They want to find projects with no children projects.
    – sfdcfox
    May 26, 2021 at 13:02
  • @sfdcfox. Yes, could you please suggest on how to achieve the required query
    – SFDCUser
    May 26, 2021 at 13:15
  • Unsure of your API names, have you tried the following replacing the spurious field and object API names with the correct values? select Id from Parent where Id not in (select ParentId from Child)
    – TSmith
    May 26, 2021 at 13:26

2 Answers 2

0

Looks like its a limitation that you cannot use INNER and OUTER select queries on the same object

You can use the below apex code,

Set<Id> projects = new Set<Id>();
for(Project__c prj: [SELECT Id, Name, (Select Id FROM Projects__r) FROM Project__c]) {
    if(prj.Projects__r.size() == 0) {
       projects.add(prj.Id);
    }

}

A couple of things to consider

  • Limit or filter the query as it might hit the number of query rows limit
  • Filtering will also reduce possibility of hitting HEAP size limit
0

The error itself is pretty descriptive. You can not use inner and outer selects on the same object type. What you can do is

List<Project__c> allProjects = [SELECT Parent_Project__c FROM Project__c];

Set<Id> projects = new Set<Id>();

for(Project__c project: allProjects ) {    
       projects.add(project.Parent_Project__c);    
}

And then

SELECT Id, Name FROM Project__c WHERE Id NOT IN:projects
1
  • check the answer below, It takes two different queries and can be merged with one May 26, 2021 at 13:49

You must log in to answer this question.

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