Query all Subjects that have no children

Question

I'm trying to write a FetchXML query that produces a result set containing all Subjects that do not have any child subjects (or equivalently, subjects not used as the parent of another). These "leaf" subjects can occur at any level in a multi-level Subject hierarchy, and are the ones users should be selecting for cases (incidents). It is impractical to collapse the Subject hierarchy to a single level in CRM itself, but a list of valid subjects is needed for an application that uses the CRM data services.

MSDN provides examples of using left outer joins to find records that are not used in a reference field of another entity (for instance, all accounts that don't have an originating lead), but for the life of me I can't make it work with Subjects.

My current best attempt is this:

@"<fetch mapping='logical'>
    <entity name='subject'>
        <attribute name='subjectid' />
        <attribute name='title' />
        <link-entity name='subject' alias='child' from='parentsubject' to='subjectid' link-type='outer'>
            <attribute name='subjectid' />
            <attribute name='title' />
            <filter type='and'>
                <condition entity='child' attribute='subjectid' operator='null'/>
            </filter>
        </link-entity>                        
        <order attribute='title' />                    
    </entity>
</fetch>"

This query produces a result set containing all subjects, as if the linked entity section doesn't exist.

Removing the filter produces something of a cross join; all subjects that have children are joined to each of their children (but do not appear without a child reference), then all subjects that do not have children are also present. I want only the second category, but adding the filter to remove subjects with children shows those subjects as if they don't have any children.

Help!

Answer 1

Your example (accounts without originating lead) is the exact opposite of what you want to achieve: in the example, the "parent" is the lead and you're looking for child records (account) with no parent (lead).

CRM doesn't support this kind of query, sadly...

A very rough way to achieve this could be (pseudo-logic)

1. Fetch all subjects (let's name this list LIST1) 
2. From LIST1, pluck out the (distinct) values of parentsubjectid attributes. Let's call this LIST2
3. From LIST1, remove all records where ID is included in LIST2
4. LIST1 now contains all records which aren't linked as parentsubject in any record