Sign in
Microsoft.com
 
Microsoft
 
 
 

locked
SQL query to fetch Related Contacts of an Account of M:M relationships in CRM 2011 ? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

  • Question
    Sign in to vote
    3
    Sign in to vote

    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

  • Question
    Sign in to vote
    3
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    You can use UNION Operation with same field names between sql statements like:-

    SELECT City FROM FilteredAccount

    UNION
    SELECT City FROM FilteredContact

    ORDER BY City;

    Regards Faisal

    Tuesday, August 27, 2013 1:07 PM