function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
developeronedeveloperone 

Filter by comparing a parent and child field

hello,

 

i'm wondering if it is possible to filter by comparing a field in the parent and a field in child.  Example, I have a parent object Merchandise that has field Price and it has a child object Line Items with Unit Price.  I was thinking...

 

SELECT Unit_Price__c, Merchandise.Price__c FROM Line_Item__c 

          WHERE Merchandise.Price__c < Unit_Price__c

 

However, it doesn't seem to allow that.  Is that a limitation or I just got the wrong query?

 

Thanks! 

AmmukuttiAmmukutti

Hai,

    I think you have to use query like this,

 

SELECT Unit_Price__c, Merchandise.Price__c FROM Line_Item__c 

          WHERE Merchandise.Price__c < Merchandise.Unit_Price__c

 

Check it out...

rocwilcoxrocwilcox

You will want to use the relationship name to reference the parent object from the child.

This are by default (unless you change the field name of the lookup field) are the name of the parent table + "__c" for the field and "__r" to address the relationship.. thus:

   WHERE Merchandise__r.Price__c < Unit_Price__c