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!