Answered by:
SQL query to fetch Related Contacts of an Account of M:M relationships in CRM 2011 ?

Question
-
Hi,
how can i fetch related Contacts of a Perticular Account in SSRS Report of having M:M relationships.?
I have created Account Detail Report in which i am showing my all Account details in one Table through FilteredAccount table..
Now i want to fetch its related Contacts in the same table.
So can anyone help me in SQL query for this ?
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"
Monday, August 26, 2013 9:56 AM
Answers
-
Hi,
Join in the intersect table. The name of the intersect table can be found in the "Relationship Entity Name" field of the N:N relationship form. The SQL will be something like:
SELECT c.contactid, c.firstname, c.lastname FROM FilteredContact c INNER JOIN Filterednew_account_contact ac ON c.contactid = ac.contactid --Replace Filterednew_account_contact with your relationship intersect name WHERE ac.accountid = @accountid
Michael Palmer
xRMPalmer
@MJFPalmer
Rockstar365
- Marked as answer by DynamicsCRM31 Friday, September 6, 2013 7:34 AM
Monday, August 26, 2013 12:46 PM
All replies
-
Hi,
Join in the intersect table. The name of the intersect table can be found in the "Relationship Entity Name" field of the N:N relationship form. The SQL will be something like:
SELECT c.contactid, c.firstname, c.lastname FROM FilteredContact c INNER JOIN Filterednew_account_contact ac ON c.contactid = ac.contactid --Replace Filterednew_account_contact with your relationship intersect name WHERE ac.accountid = @accountid
Michael Palmer
xRMPalmer
@MJFPalmer
Rockstar365
- Marked as answer by DynamicsCRM31 Friday, September 6, 2013 7:34 AM
Monday, August 26, 2013 12:46 PM -
Can you provide me the same query combined with FilteredAccount,
so that i can put that query in one single table and can get Accounts and Contacts in one table.
Otherwise it would become cumbersome to handle 2 tables in grouping.
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"
Tuesday, August 27, 2013 9:59 AM -
Hi
you can use FetchXML query
<fetch distinct="false" no-lock="false" mapping="logical">
<entity name="account" enableprefiltering="1" prefilterparametername="CRM_FilteredAccount">
<attribute name="name" alias="name" /><attribute name="accountnumber" alias="accountnumber" />
<attribute name="accountid" />
<link-entity name="contact" to="accountid" from="parentcustomerid" link-type="outer" alias="contact1" enableprefiltering="1" prefilterparametername="CRM_FilteredContact">
<attribute name="fullname" alias="contact1_fullname" />
<attribute name="telephone1" alias="contact1_telephone1" /><attribute name="contactid" /></link-entity></entity></fetch>
Tuesday, August 27, 2013 1:06 PM -
You can use UNION Operation with same field names between sql statements like:-
SELECT City FROM FilteredAccount
UNION
SELECT City FROM FilteredContactORDER BY City;
Regards Faisal
Tuesday, August 27, 2013 1:07 PM