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,

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
  • 1
    8 years for Salesforce to put a community-sourced idea on the roadmap...seems about par for the course. – Derek F Apr 27 '18 at 19:55
  • Welcome to Salesforce world :) You can find many that many years old ideas, still waiting to be responded from Salesforce. – realfire Apr 27 '18 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);    
        } 
    }
}
  • 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? – Mike Marshall Apr 27 '18 at 20:27
  • Can you post your code? – the_phantom Apr 27 '18 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 – Mike Marshall Apr 30 '18 at 12:30
  • Notes do not seem to like code. The original is posted in the OP. – Mike Marshall Apr 30 '18 at 12:32
  • What is the error? – the_phantom Apr 30 '18 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.