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
Ravi KothariRavi Kothari 

how to access parent fields on soql where condition ?

I am trying to compare parent name & child name field in soql where item is parent, Item cost is child and have a lookup relationship.

Below is my query :-
SELECT Id, Name, KNDY4__Item__c, KNDY4__Item__r.Name FROM 
KNDY4__Item_Cost__c where KNDY4__Item__r.Name <> Name

but its giving me parcing error. 

please correct me where its wrong.
William TranWilliam Tran
Is the name correct?

KNDY4__Item__c, should it be 
KNDY4_Item__c,

that is remove the __ to _, unless it is __c or __r 

Change that everywhere

As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you. 

Thanks
Ravi KothariRavi Kothari
hey William, since its a package field therefore you see it a s"__" 
William TranWilliam Tran
Okay Ravi,

Salesforce doesn't allow direct field to field comparison in SOQL query.

To achieve this you may create a formula field that will compare fields and return a value (like true or false) which you may use in a WHERE clause.

So for the above query, you could create a formula field on User object with return type Text e.g. NameCompare, with the formula IF(User.FirstName != User.LastName, 'true', 'false')

Now our query will be:

List<User> Users = [SELECT id, name FROM User where NameCompare= 'true'];

Following idea has been posted on ideaexchange portal for allowing field-to-field comparison in SOQL:

https://success.salesforce.com/ideaView?id=08730000000BrHAAA0
Abhishek BansalAbhishek Bansal
Hi Ravi,

You can use the below code to acheive what you want :
  
List<KNDY4__Item__c> itemList = new List<KNDY4__Item__r>([Select Name from KNDY4__Item__c]);

Set<String> nameSet = new Set<String>();

for(KNDY4__Item__c item : itemList){
	nameSet.add(item.Name);
}

List<KNDY4__Item_Cost__c> itemCostList = new List<KNDY4__Item_Cost__c>([SELECT Id, Name, KNDY4__Item__c, KNDY4__Item__r.Name FROM KNDY4__Item_Cost__c where Name NOT IN :nameSet]);

Let me know if you need any help on this.

Thanks,
Abhishek