+ Start a Discussion

SOQL Relationship Confusion, please help.

I'm pretty new to the SOQL world and I am having trouble with a relationship query. I am trying to return the OpportunityID of an opportunity associated with a specific account and having a specific partner. Below is the query, any help is appreciated. sellerID and buyerID are variables holding salesforce record ids for the appropriate accounts. 




"select o.Id from Opportunity o where o.AccountID = '"+sellerID+"' and o.Partners.AccountFromID ='"+buyerID+"'";





When I run the query I get the following error.


 Didn't understand relationship 'Partners' in field path. If you are attempting to use a custom relationship be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.


I am integrating from .NET code using the Enterprise WSDL. When I look at the WSDL or the autocomplete in Visual Studio I see the Partners relationship with the return type of 'QueryResult'. However when I do a describeSObject("Opportunity") I don't see it. Any ideas?


Thanks again for your help. 


The opportunity -> partners relation is a child relationship, i.e. for a given opportunity there can be many partner rows, that why your query doesn't work (and that's why the partners element in the wsdl is a queryResult, because there can be many rows). assuming you don't care which in particular partner row matches, just any partner row, you can do this with a semi-join, something like


select Id from opportunity where accountid='someId' and Id in (select opportunityId from partner where accountFromId='someId')