How could I fetch data using left outer join in Fetch XML?

Question

I could make columns, but couldn't display data.

I'm building SSRS reports with Visual Studio 2008, and CRM version is CRM 2016 Online.

This fetch XML query doesn't display data which is in link-entity, 'meeting'.

<fetch mapping='logical'>                   
<entity name='company'>             
    <attribute name='name'/>            
    <attribute name='createdon'/>           
    <attribute name='companyid'/>           
    <order descending="false" attribute="name"/>            
    <filter type="and">         
            <condition attribute="infocode" value="0" operator="eq"/>   
    </filter>           
    <link-entity name='company' from='companyid' to='meetingid' link-type='outer'>          
        <attribute name='meetingid' />      
        <attribute name="topic"/>       
        <attribute name="createdon"/>       
        <order descending="false" attribute="topic"/>       
    </link-entity>          
</entity>

For further details - entities configurations displayed and desired display of data - please see this image.

score 1 · Answer 1 · answered Aug 02 '16 at 22:50

1

Based on the picture you posted in the question, I think you need to do a INNER JOIN to show data from both tables.

Replace this line:

<link-entity name='company' from='companyid' to='meetingid' link-type='outer'>

By:

<link-entity name='company' from='companyid' to='meetingid' link-type='inner'>

LEFT OUTER JOIN returns rows from one table that are not related with rows from other table. Since you want to join both entities it is not suited for your case.

You can use a left outer join in FetchXML to perform a query that filters on the join table, such as to find all contacts who did not have any campaign activities in the past two months.

REFERENCE

Let me know if this helps.

answered Aug 02 '16 at 22:50

alejandro zuleta

13,962
3
28
48

Hi, alejandro. I tried replace 'outer' by 'inner', columns made but I couldn't see values. ("AA.inc","08/02/2016","BZ.inc","3156",etc...) (couldn't display.) Is Fetch-XML( or "CRM 2016 online") support Inner-Join? I couldn't find the article in Web about that... Now, I'm making report with "Visual studio 2008" and "CRM 2016 online", do I need install "Dynamics CRM 2016 SDK"? – esperanish Aug 03 '16 at 01:33
@esperanish, check this [Use FetchXML to construct a query](https://msdn.microsoft.com/en-us/library/gg328117.aspx). It is applied to Microsoft Dynamics CRM 2016 and Microsoft Dynamics CRM Online. – alejandro zuleta Aug 03 '16 at 02:17

Dev · Answer 2 · 2016-09-12T19:21:11.253

esperanish, Yes FetchXML supports inner-join operation.

Have you created the above FetchXML manually? or used advance find view in Dynamics CRM online?.

I suggest you to use advance find view to create the FetchXML, which is more accurate.

On your given FethXML code I can see that your main entity is "Company" and your Link entity is "Company" too, linking to the same entity doesn't make any sense here.

try using these FetchXML:

    <fetch mapping='logical'>                   
<entity name='company'>             
    <attribute name='name'/>            
    <attribute name='createdon'/>           
    <attribute name='companyid'/>           
    <order descending="false" attribute="name"/>            
    <filter type="and">         
            <condition attribute="infocode" value="0" operator="eq"/>   
    </filter>           
    <link-entity name='meeting' from='meetingid' to='companyid' link-type='inner'>          
        <attribute name='meetingid' />      
        <attribute name="topic"/>       
        <attribute name="createdon"/>       
        <order descending="false" attribute="topic"/>       
    </link-entity>          
</entity>

or

    <fetch mapping='logical'>                   
<entity name='company'>             
    <attribute name='name'/>            
    <attribute name='createdon'/>           
    <attribute name='companyid'/>           
    <order descending="false" attribute="name"/>            
    <filter type="and">         
            <condition attribute="infocode" value="0" operator="eq"/>   
    </filter>           
    <link-entity name='meeting' from='companyid' to='meetingid' link-type='inner'>          
        <attribute name='meetingid' />      
        <attribute name="topic"/>       
        <attribute name="createdon"/>       
        <order descending="false" attribute="topic"/>       
    </link-entity>          
</entity>

Note: Am not sure if the FetchXML given by me will work for you or not because I don't know the mapping used in your CRM. You can consider it as an example to workaround.

How could I fetch data using left outer join in Fetch XML?

2 Answers2