Are composite joins possible using FetchXml in Microsoft Dynamics CRM 4.0?

Question

I am using FetchXml to query CRM 4.0. We have a special case that will require a composite join between CRM entites. The FetchXml schema indicates that multiple link-entity elements are allowed, and it also indicates that multiple filter/condition elements can be added to a link-entity. The problem I'm facing is that the value attribute of the condition element does not appear to permit an entity/column name. It expects an explicitly declared value.

For example, FetchXml lets you specify this:

<link-entity name='myentity' from='column1' to='column2'/>

... which does the T-SQL equivalent of this:

JOIN myentity on column1 = column2

And it lets you specify this:

<link-entity name='myentity' from='column1' to='column2'>
   <filter type='and'>
      <condition attribute='column3' operator='eq' value='myvalue' />
   </filter>
</link>

... which is the T-SQL equivalent of this:

JOIN myentity on column1 = column2 AND column3 = 'myvalue' 

FetchXml does not appear, however, to provide an equivalent of this:

JOIN myentity on column1 = column2 AND column3 = column4 

Note the difference. FetchXml provides for conditions in the join, but it does appear to provide for a composite join, that is, a join across multiple columns.

Has anyone out there in cyberspace been able to perform a composite join using FetchXml in CRM 4.0? Thanks!

More information:

I'm hunting an answer that uses FetchXml to accomplish this - not SQL or the QueryExpression syntax. The SQL above is there just to explain the concept.

Answer 1

No, it doesn't permit this. Fetch XML is pretty limited when it comes to anything non-basic in joins. If I'm curious I usually test my query using Stunnware Tools. If it is not exposed there it probably can't be done.

Unfortunately, in situations like these these I usually end up (am forced into) taking a multiple query approach to the problem.

I know you said you're not interested in this - but I'm pretty sure QueryExpression won't handle it either. In my experience it only offers a subset of the fetchxml functionality.