locked
Need help on writing query RRS feed

  • Question

  • Hi all,

     

    I have created a new relationship between the contact and user entities called support_manager

     

    I need to select account name that is a parent of contact that have a support manager id = '{F31D9D73-EA8C-DF11-9F21-00215A1F6AE3}'

     

    i have written this query that i think it must be the correct query:

     

    select AccountId,Name

    from account join contact

    where support_manager = '{F31D9D73-EA8C-DF11-9F21-00215A1F6AE3}'

     

    does this query fit my needs cause i have test it it doesn't display the requested record

     

    if no please help me to write the correct query

    using sql or fetch xml

     

    any help will be appreciated,

     

    thanks in advance :)

    Monday, October 4, 2010 1:20 PM

Answers

All replies

  • Hi all,

     

    I have created a new relationship between the contact and user entities called support_manager

     

    I need to select account name that is a parent of contact that have a support manager id = '{F31D9D73-EA8C-DF11-9F21-00215A1F6AE3}'

     

    i have written this query that i think it must be the correct query:

     

    select AccountId,Name

    from account join contact

    where support_manager = '{F31D9D73-EA8C-DF11-9F21-00215A1F6AE3}'

     

    does this query fit my needs cause i have test it it doesn't display the requested record

     

    if no please help me to write the correct query

    using sql or fetch xml

     

    any help will be appreciated,

     

    thanks in advance :)

    • Merged by DavidJennawayMVP, Moderator Thursday, October 21, 2010 2:34 PM Duplicate thread. Please only post the question in one forum
    Monday, October 4, 2010 1:22 PM
  • Hi Mostafa,

    You can use the below SQL query:

     

    SELECT   FilteredAccount.accountid, FilteredAccount.name, FilteredContact.parentcustomerid, FilteredContact.fullname
    FROM     FilteredAccount INNER JOIN
               FilteredContact ON FilteredContact.parentcustomerid = FilteredAccount.accountid
    WHERE   (FilteredContact.support_manager = '{F31D9D73-EA8C-DF11-9F21-00215A1F6AE3}')
    

     


    http://mscrmblog.net
    Microsoft Certified Business Management Solutions Specialist
    Microsoft Certified CRM Developer
    • Proposed as answer by Ibrahim Sukari Monday, October 4, 2010 1:40 PM
    Monday, October 4, 2010 1:33 PM
  • Thanks alot ibbz that was really helpful

    Monday, October 4, 2010 1:39 PM
  • Dear ibbz,

     

    I have only one question, if i need to convert the query into a fetch xml query,,,which of the account and contact entities should i use ??

    Monday, October 4, 2010 1:58 PM
  • Dear ibbz,

     

    I have written the following quey based on your query, but unfortunately it doesn't display the results:

     

    <q1:EntityName>account</q1:EntityName>

    <q1:ColumnSet xsi:type=\"q1:ColumnSet\"> 

    <q1:Attributes> 

    <q1:Attribute>name</q1:Attribute> 

    <q1:Attribute>accountid</q1:Attribute>

    </q1:Attributes>

    </q1:ColumnSet>

    <q1:LinkEntities> 

    <q1:LinkEntity> 

    <q1:LinkFromAttributeName>accountid</q1:LinkFromAttributeName> 

    <q1:LinkFromEntityName>account</q1:LinkFromEntityName>

    <q1:LinkToEntityName>contact</q1:LinkToEntityName> 

    <q1:LinkToAttributeName>parentcustomerid</q1:LinkToAttributeName> 

    <q1:JoinOperator>Inner</q1:JoinOperator>

    <q1:LinkCriteria>

    <q1:FilterOperator>And</q1:FilterOperator> 

    <q1:Conditions>

    <q1:Condition> 

    <q1:AttributeName>support_manager</q1:AttributeName> 

    <q1:Operator>Equal</q1:Operator>

    <q1:Values>

    <q1:Value xsi:type=\"xsd:string\">'{F31D9D73-EA8C-DF11-9F21-00215A1F6AE3}'</q1:Value> 

    </q1:Values> 

    </q1:Condition> 

    </q1:Conditions> 

    <q1:Filters />

    </q1:LinkCriteria> 

    </q1:LinkEntity> 

    </q1:LinkEntities> 

    <q1:Criteria>

    <q1:FilterOperator>And</q1:FilterOperator> 

    <q1:Conditions />

    <q1:Filters /> 

    </q1:Criteria> 

    <q1:Orders /> 

     

    could you please help me on writing the right query

     

    thanks in advance :)


     

    Monday, October 4, 2010 2:54 PM
  • Hi Mostafa,

    You need to download fetxml builder(a free tool) and make queries using fetch xml. You can also convert them to query expression for using them in C# or VB.net.

    Regards

    Faisal

    Monday, October 4, 2010 3:12 PM
  • any help ???
    Monday, October 4, 2010 5:46 PM