Need help on writing query

All replies

• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  Thanks alot ibbz that was really helpful

  • Marked as answer by Mostafa Moatassem Monday, October 4, 2010 1:39 PM

  Monday, October 4, 2010 1:39 PM
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  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
• 

  

  0

  Sign in to vote

  any help ???

  Monday, October 4, 2010 5:46 PM