+ Start a Discussion

Best Way to Query Contacts Based on Opportunites

I have a rather complicated situation and i'm not sure if this can be done in a single SOQL query and was hoping a guru here might have some insight. I need to


** Query All contacts that are owned by a specific user and the contact's account has at least 1 opportunity in the specified stage


Currently I'm

1. Querying all the contacts that are owned by a specific user 

2. Gathering all the account id's into a set

3. Querying all opportunities with the desired stage

4. Filtering out contacts where their account doesn't have any opportunities returned


Issue with above

A user may have many 1000's of contacts. So you cannot querying all the contacts and opportunites as you will go above 10k records returned. So you query 2000 contacts (perhaps of 9000) and all 2000 are filtered out as none are associated with an account with an opportunity in the desired stage. So you end up w/ no contacts even though there may some in the remaining 7000 un-queried contacts.


Is it possible to run this in a single query?


like this? 

select lastname from contact where owner.username = <userName> and account.name in (select account.name from opportunity)


I don't have access to my Co. Opportunity, so I could not test the last part.

Should traverse the Opportunities and return a list of all associated Accounts.  Then just compare to the Contact's Account.