function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Nathan Prats 17Nathan Prats 17 

SOQL Query - Opportunity Owner NOT EQUALS TO Account Owner

Hi, 

I'm trying to build a query to returns a list of Opportunity IDs where the Opportunity Owner doesn't match the Account Owner. 

I've tried this : 
SELECT Id FROM Opportunity WHERE OwnerId != Opportunity.Account.OwnerId. 
But it doesn't work.

Any Idea ? 

Best Answer chosen by Nathan Prats 17
LBKLBK
Unfortunately, field-to-field comparison does not work in SOQL.

What you need is a formula (Checkbox or Boolean type) field on Opporutnity Object, which will have the following formula.
  
if(OwnerId = Account.OwnerId, true, false)
If you name your new formula field as Ownership_Check__c, your SOQL query would be as follows. 
SELECT Id FROM Opportunity WHERE Ownership_Check__c = false
Hope this helps.
 

All Answers

LBKLBK
Unfortunately, field-to-field comparison does not work in SOQL.

What you need is a formula (Checkbox or Boolean type) field on Opporutnity Object, which will have the following formula.
  
if(OwnerId = Account.OwnerId, true, false)
If you name your new formula field as Ownership_Check__c, your SOQL query would be as follows. 
SELECT Id FROM Opportunity WHERE Ownership_Check__c = false
Hope this helps.
 
This was selected as the best answer
Swathi MiryalaSwathi Miryala
Alternatively you could use this
SELECT Id, Name, OwnerId FROM Opportunity WHERE OwnerId NOT IN (SELECT OwnerId FROM account)
LBKLBK
Hi Swathi,

If a user owns an Account1 and creates Opportunity2 on Account2 (owned by someone else), Opportunity2 will not be returned by your SOQL query.
I guess Nathan needs Opportunity2 listed because the opportunity and the related account are owned by two different users.
SScholtzSScholtz
Hi Nathan,

Unfortunately I don't think this can be done in a single shot SOQL query anyways. I'm not going to have the right technical explanation here, but your WHERE clause can't compare and field value against another field value.  You have to compare it against a specific value or list of values or another query. (see "semi-joins" here (https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_comparisonoperators.htm))

An alternative though is to create a custom formula field on your opportunity that compares the owner Ids and returns a boolean.  Something like...

OwnerId = Account.OwnerId

...then use that in your query...

SELECT Id FROM Opportunity WHERE Opp_Owner_Equal_Account_Owner__c != true

You can come up with a better name for your custom formula field of course. :) Using formula fields in this way is a long time common solution for some of the trickier queries you may need to do.
 
Suvankar Chakraborty 21Suvankar Chakraborty 21
NATHAN, 

Use this soql query.It will work perfectly.

SELECT Id, Name, OwnerId FROM Opportunity WHERE OwnerId  NOT IN (SELECT OwnerId FROM account)