+ Start a Discussion
Robbert Bos2Robbert Bos2 

SOQL where statement in list

I have a public list where in I want to check if the record owner is the same as a related object, but I keep getting an error. Anybody know if this is possible?
 
public List< Custom__c > getBalance2(){
        return [SELECT id, name, State__c, OwnerId, Related__r.OwnerId

                    FROM Custom__c 
                    WHERE State__c = 'Active'
                    AND Related__r.OwnerId =  :Balance2.OwnerId ];
}

 
Best Answer chosen by Robbert Bos2
Alain CabonAlain Cabon
Hi,

The wrong workaround is the following requests because it is any owner in the two objects (bad):
  1. select id, ownerid , firstname,lastname from contact where ownerid in (select ownerid from account)
  2. select id, ownerid,  firstname,lastname from contact where ownerid not in (select ownerid from account)
1. the same owner can be not on the parent account (it lacks the join)
2. seems better but too restrictive (it also lacks the join) 

The common technique when you want to compare any fields is to use formula fields (checkbox) building cross-object formulas.

The field formula here in custom object is : isEqualsParentOwner__c =  Related__r.OwnerId =  OwnerId 

SELECT id, name, State__c, OwnerId, Related__r.OwnerId                    
FROM Custom__c                     
WHERE State__c = 'Active'                    
AND  isEqualsParentOwner__c  = true

The only problem is the performance with these formulas.

Bad:  Unknown error parsing query
select id, account.ownerid, ownerid
from contact
where  account.ownerid = ownerid 

Correct:
select id, account.ownerid, ownerid
from contact
where isEqualsParentOwner__c = true

with isEqualsParentOwner__c : a new formula field (checkbox) for Contact =  account.ownerid = ownerid 

And finally, you can use:only one request:

select id, account.ownerid, ownerid, firstname,lastname, isEqualsParentOwner__c
from contact

Regards

All Answers

sunny.sfdcsunny.sfdc
What is Balance2 and what is the error you are getting?
Alain CabonAlain Cabon
Hi,

The wrong workaround is the following requests because it is any owner in the two objects (bad):
  1. select id, ownerid , firstname,lastname from contact where ownerid in (select ownerid from account)
  2. select id, ownerid,  firstname,lastname from contact where ownerid not in (select ownerid from account)
1. the same owner can be not on the parent account (it lacks the join)
2. seems better but too restrictive (it also lacks the join) 

The common technique when you want to compare any fields is to use formula fields (checkbox) building cross-object formulas.

The field formula here in custom object is : isEqualsParentOwner__c =  Related__r.OwnerId =  OwnerId 

SELECT id, name, State__c, OwnerId, Related__r.OwnerId                    
FROM Custom__c                     
WHERE State__c = 'Active'                    
AND  isEqualsParentOwner__c  = true

The only problem is the performance with these formulas.

Bad:  Unknown error parsing query
select id, account.ownerid, ownerid
from contact
where  account.ownerid = ownerid 

Correct:
select id, account.ownerid, ownerid
from contact
where isEqualsParentOwner__c = true

with isEqualsParentOwner__c : a new formula field (checkbox) for Contact =  account.ownerid = ownerid 

And finally, you can use:only one request:

select id, account.ownerid, ownerid, firstname,lastname, isEqualsParentOwner__c
from contact

Regards
This was selected as the best answer