Answered by:
Need help on writing query

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
-
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
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
- Marked as answer by Mostafa Moatassem Monday, October 4, 2010 1:39 PM
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