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.