Apex Code Development You need to sign in to do that
Don't have an account?
Palmira Angelova How to write an SOQL query that looks up child to parent to child?
Hi friends,
I need to look up specific Contacts on Accounts, but only include Contacts on Accounts that have Opportunities WHERE Stage IN('X','Y','Z') i.e. what I think of as a child -> parent -> child lookup. I've been using workbench for this.
I couldn't figure out a way to do this within the syntax, so instead I opted for Account -> Contacts WHERE Account -> Opportunities meets my criteria, which seemed to make sense to me. However, my query is getting an error telling me I can't have a child -> parent lookup in the where clause:
"MALFORMED_QUERY:
AND Id IN(SELECT Account.Id FROM Opportunity WHERE
ERROR at Row:5:Column:24
The inner select field 'Account.Id' cannot have more than one level of relationships"
Here is my query:
SELECT Id,
(SELECT Id FROM Contacts WHERE Send_Partner_Updates__c = TRUE)
FROM Account
WHERE Account.POS_Provider_s__c NOT IN('Toshiba ACE', 'Toshiba ACE (old, non-integrated)')
AND Id IN(SELECT Account.Id FROM Opportunity WHERE Stage IN('6 - Deployed', '6 - Deploying', '6.5 - Fully Deployed'))
Does anyone have a way to get by this? When I tried "SELECT Id FROM Opportunity", removing the child->parent lookup within the Opportunity query, I get this error: "The selected field 'Id' in the subquery and the left operand field in the where expression in the outer query 'Id' should point to the same object type"...so I haven't been able to think of a way around these two limitations.
Thanks for your help!
I need to look up specific Contacts on Accounts, but only include Contacts on Accounts that have Opportunities WHERE Stage IN('X','Y','Z') i.e. what I think of as a child -> parent -> child lookup. I've been using workbench for this.
I couldn't figure out a way to do this within the syntax, so instead I opted for Account -> Contacts WHERE Account -> Opportunities meets my criteria, which seemed to make sense to me. However, my query is getting an error telling me I can't have a child -> parent lookup in the where clause:
"MALFORMED_QUERY:
AND Id IN(SELECT Account.Id FROM Opportunity WHERE
ERROR at Row:5:Column:24
The inner select field 'Account.Id' cannot have more than one level of relationships"
Here is my query:
SELECT Id,
(SELECT Id FROM Contacts WHERE Send_Partner_Updates__c = TRUE)
FROM Account
WHERE Account.POS_Provider_s__c NOT IN('Toshiba ACE', 'Toshiba ACE (old, non-integrated)')
AND Id IN(SELECT Account.Id FROM Opportunity WHERE Stage IN('6 - Deployed', '6 - Deploying', '6.5 - Fully Deployed'))
Does anyone have a way to get by this? When I tried "SELECT Id FROM Opportunity", removing the child->parent lookup within the Opportunity query, I get this error: "The selected field 'Id' in the subquery and the left operand field in the where expression in the outer query 'Id' should point to the same object type"...so I haven't been able to think of a way around these two limitations.
Thanks for your help!
Change the Account.Id to AccountId in the subquery for Opportunity hence your updated Soql query will now become
SELECT Id, (SELECT Id FROM Contacts WHERE Send_Partner_Updates__c = TRUE) FROM Account WHERE Account.POS_Provider_s__c NOT IN('Toshiba ACE', 'Toshiba ACE (old, non-integrated)') AND Id IN (SELECT AccountId, Stage FROM Opportunity WHERE Stage IN('6 - Deployed', '6 - Deploying', '6.5 - Fully Deployed'))Hope that helps.All Answers
Change the Account.Id to AccountId in the subquery for Opportunity hence your updated Soql query will now become
SELECT Id, (SELECT Id FROM Contacts WHERE Send_Partner_Updates__c = TRUE) FROM Account WHERE Account.POS_Provider_s__c NOT IN('Toshiba ACE', 'Toshiba ACE (old, non-integrated)') AND Id IN (SELECT AccountId, Stage FROM Opportunity WHERE Stage IN('6 - Deployed', '6 - Deploying', '6.5 - Fully Deployed'))Hope that helps.Thank you, @jigarshah for your query.
For any whom it helps, here is basically my query as a second example, with a metaphor to apply to your situation: We're starting at a child, up to the parent and down to a different record from the starting child's related list, mixing standard and custom objects.
Imagine you have a knife in your hand (or a record about a knife, Id = 'b4h4k00EKNIFEEIDx' haha). You need to determine if this is a steak knife, chef knife or a butter knife. The certain way to determine that is to know if your knife came from an Account (i.e. Silverware Drawer) that has a Fork in it. This query would give you the information about any forks in the Account (Drawer) that you got the knife from.
I solved my issue with it, when I created an app in https://joinsoft.com/services/web-application-development/
In this query, we are retrieving the Name field from the Account object and the LastName field from the child Contact object. We are querying all accounts where the Industry field is equal to 'Technology'.
This query follows a relationship path from Account to Contact, where Account is the parent object and (https://procreatemac.com/)is the child object. The (SELECT LastName FROM Contacts) part in the query represents the subquery that retrieves the related child records. (https://procreatemac.com/)
Please note that the actual field names and relationship names may vary based on your specific Salesforce org's schema.