how to prevent a crossjoin in fetchxml?



when running the following query:



<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
<entity name="account">
<attribute name="name" alias = "company_name" groupby="true"/>
<attribute name="bio_employeesworldwide" alias="TotalWorldWideEmployees" aggregate="sum" />
<filter>
<condition attribute="customertypecode" operator="eq" value="2" />
</filter>



</entity>
</fetch>


the results that i get are very accurate


however, i am trying to get more accurate results, by joining onto another entity and filtering by a a specific condition:



<link-entity name="contact" from="parentcustomerid" to="accountid" alias="contact">

<attribute name="bio_webuseraccountstatus" alias="TotalActive" aggregate="countcolumn" />

<filter>
<condition attribute="bio_webuseraccountstatus" operator="eq" value="3" />
</filter>

</link-entity>


as you can see i linked to the entity called contact and i need the bio_webuseraccountstatus = 3


the full script is:



<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
<entity name="account">
<attribute name="name" alias = "company_name" groupby="true"/>
<attribute name="bio_employeesworldwide" alias="TotalWorldWideEmployees" aggregate="sum" />
<filter>
<condition attribute="customertypecode" operator="eq" value="2" />
</filter>
<link-entity name="contact" from="parentcustomerid" to="accountid" alias="contact">

<attribute name="bio_webuseraccountstatus" alias="TotalActive" aggregate="countcolumn" />

<filter>
<condition attribute="bio_webuseraccountstatus" operator="eq" value="3" />
</filter>

</link-entity>



</entity>
</fetch>


when running the first script i get about approximately "x" number of counts per record, where as when i run the entire query, i get approximately 10-20x of counts per record


what could be causing this?


i am thinking that i am doing an inner join when i am adding the contact entity, but it seems like it's doing a cross join? what am i doing wrong?


No comments:

Post a Comment