3

I'm new to SOQL coming from a SQL background, and need to create a query that will pull in all Contact records where the OwnerID does not match the OwnerID of the Account.

Here is what I have so far:

SELECT a.ID, a.OwnerID, 
(
SELECT c.OwnerID, c.ID 
FROM Contacts c
)  FROM Account a

I've found lots of examples for "WHERE Name like 'Marshall%' etc, but what I need is to translate this statement in SQL to SOQL:

WHERE c.OwnerID <> a.OwnerID

I know this is not how it's done in SOQL, and am looking for how to write it properly. I have not found an answer to this in Trailhead, Salesforce boards, etc, and I may not know how to phrase the question. Any help would be appreciated.

Thanks,

3 Answers 3

4

First, go vote for this idea. Second, to get a native query that works, you can write a formula on the Contact record that reads:

Account Owner Owns Contact(Checkbox):

OwnerId = Account.OwnerId

Then, you write a query like this:

SELECT Id, AccountId FROM Contact WHERE Account_Owner_Owns_Contact__c = FALSE
2
  • 1
    8 years for Salesforce to put a community-sourced idea on the roadmap...seems about par for the course.
    – Derek F
    Apr 27, 2018 at 19:55
  • Welcome to Salesforce world :) You can find many that many years old ideas, still waiting to be responded from Salesforce.
    – metasync
    Apr 27, 2018 at 21:43
3

SOQL cannot be used to construct complex queries like SQL. You should try something like this, if you need to compare OwnerId between Account and its related Contacts.

for(Account acc : [SELECT Id, Name, OwnerId, (SELECT Id, FirstName, AccountId, OwnerId From Contacts) From Account LIMIT 5]) {
    for(Contact con : acc.Contacts) {
        if(acc.OwnerId != con.OwnerId) {
            System.debug('Contact record: ' + con);    
        } 
    }
}
11
  • If I try to run this as posted above I get the error "Query has to start with FIND or SELECT." I get same error if I run this stand alone or with my code above. Can you point me why I'm missing? Apr 27, 2018 at 20:27
  • Can you post your code? Apr 27, 2018 at 22:36
  • I tried running the code exactly as you posted it, and got the above errors. Then I tried to combine it with my existing code: SELECT a.ID, a.OwnerID, ( SELECT c.OwnerID, c.ID FROM Contacts c ) FROM Account a Apr 30, 2018 at 12:30
  • Notes do not seem to like code. The original is posted in the OP. Apr 30, 2018 at 12:32
  • What is the error? Apr 30, 2018 at 14:45
0
SELECT Id FROM Contact WHERE not (ownerId like '%yourownerId')

Mike, try using the not(ownerId like '%yourownerId') in where clause. It will give you the list that doesn't contain your ownerId.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .