+ Start a Discussion
Dave DudekDave Dudek 

SOQL query with a subquery to show contacts and accounts located within a state that have a certain custom object relationship

I'm trying to create a SOQL query with a subquery to show contacts and accounts located within a state that have a certain custom object relationship:

Here is my code:

SELECT Id,Name FROM Account WHERE Our_Role_Count__c > 0 AND BillingState = 'NH' AND Id IN (SELECT Account__r.Id,Account__r.Name,Account__r.BillingState, Our_Role__r.Name FROM Our_Role_Service_Affiliation_for_Account__c WHERE Our_Role__r.Name != 'Outreach')

However I get an error: The inner select field 'Account__r.Id' cannot have more than one level of relationships
Best Answer chosen by Dave Dudek
Tarun Khatri (TK)Tarun Khatri (TK)
Hello Dave,

You have to update inner query to keep only single field in SELECT. You cannot put more fields in such inner  query. It should contain only field which has matching object type as Id of outer query. In your case, it should be Account Id only. Below is the updated query. 
 
SELECT Id,Name FROM Account WHERE Our_Role_Count__c > 0 AND BillingState = 'NH' AND Id IN (SELECT Account__c FROM Our_Role_Service_Affiliation_for_Account__c WHERE Our_Role__r.Name != 'Outreach')

-Thanks,
TK

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.

All Answers

Tarun Khatri (TK)Tarun Khatri (TK)
Hello Dave,

You have to update inner query to keep only single field in SELECT. You cannot put more fields in such inner  query. It should contain only field which has matching object type as Id of outer query. In your case, it should be Account Id only. Below is the updated query. 
 
SELECT Id,Name FROM Account WHERE Our_Role_Count__c > 0 AND BillingState = 'NH' AND Id IN (SELECT Account__c FROM Our_Role_Service_Affiliation_for_Account__c WHERE Our_Role__r.Name != 'Outreach')

-Thanks,
TK

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved.
This was selected as the best answer
Dave DudekDave Dudek
so i've changed it a bit and have a new quesiton now...