function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Wolf Duttlinger 6Wolf Duttlinger 6 

Find parents without children for self referencing objects

Hi all,

we are using cases in various recordtypes (A, B). Every A should normally have a B as a child. I'm trying to find those that haven't one.

select case.id, case.CaseNumber from case where case.id not in (select case.parentid from case) and case.recordtype.name = 'A'
--> The inner and outer selects should not be on the same object type

select case.id, case.CaseNumber from case where case.recordtype.name = 'Service Case' and (select count() from case.cases) = 0
--> Unknown error parsing query

select id, CaseNumber from case a where not exists (select id from case b where b.parentid = b.id ) and a.recordtype.name = 'A'
--> Unknown error parsing query

Any suggestions????

Thanks
Wolf
Ashish DevAshish Dev
You could do it rather in a simple way.
  
select id, CaseNumber from case WHERE recordtype.developername = 'A' and parent.recordtype.developername != 'B'

Let me know if this solves your issue.
Wolf Duttlinger 6Wolf Duttlinger 6
Thanks for the quick answer.

Maybe I don't see it - but this doesn't solve the problem. Your example would require 'B' to exist - which should not be the case.

I have 
ID     Type      Parent
1       A            -
10     B            1
2       A            -
20     B            2
3       A            -
4       A            -
40     B            4

I need the result to be "3"

Regards
Wolf
Ashish DevAshish Dev
You could try to use below code. 
  
Map<Id,Case> csIds =  new Map<ID, Case>([select id from case where recordtype.developername = 'B']);
List<case> csList = [select id, CaseNumber from case WHERE recordtype.developername = 'A' and Id NOT IN : csIds.keySet()];
system.debug('list - ' + csList);


Let me know if this helps.
 
Wolf Duttlinger 6Wolf Duttlinger 6
Hi Ashih,

yes - coding it in APEX solves the problem. I just did hope a single SOQL would do the trick..... SQL would do it....

Wolf
Abhishek_DEOAbhishek_DEO
Hi,

Perhaps I did not understand completly your data structure but you may try below

select case.id, case.CaseNumber from case where case.recordtype.name = 'A' and parent.id=null