+ Start a Discussion
mattjones99mattjones99 

Can I get related list items that match the parent item based on some field?

Let's say I have two objects, Parent and Child, where Child is a related list of Parent.  

I can write a query to get all children named 'One' from all parents.  And this works.

SELECT Name, (SELECT Name FROM Child__r WHERE Name = 'One') FROM Parent__c

But, what if I wanted to get all children with the same name as their parent?  

I think this would look something like:  

SELECT Name, (SELECT Name FROM Child__r WHERE Name = Parent__r.Name) FROM Parent__c

But this is always returning a malformed query exception where Parent__r is an unexpected token.  Is it possible to do what I'm trying to do?  Or do I have to get the parents, then make a separate query for each parent to get the children that match that parents' name?

bob_buzzardbob_buzzard

You can't do that I'm afraid - the right hand side of the comparison has to be a literal, so you can't use other fields from the query.

 

Depending on the volumes, I often retrieve the parents and all their children, then do the checks when processing the data.  The problem with retrieving the parents and then the children is that you'll stand a good chance of running out of SOQL calls.

shra1_devshra1_dev

Hi mattjones99,

 

Yes you cannot query in that way in SOQL.

 

But you can do this by a trick to avoid script statements in your class.

 

Create a formula field on Child Object which compares child and Parent names return some string like YES/NO and use that field in the query.

 

Example:

isNameEqual__c (TEXT) : if(Name = Parent.Name, 'YES', 'NO')

 

in Query use:

SELECT Name, (SELECT Name FROM Child__r WHERE isNameEqual__c= 'YES') FROM Parent__c

 

Hope this solves ur problem..

 

 

Regards,

Shravan