Why am I getting this error? If I remove the Addressess__r references in the WHERE clause it works fine.

Error: Didn't understand relationship 'Addresses_r' in field path. If you are attempting to use a custom relationship, be sure to append the '_r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

             SELECT ParentId, 
                Id, 
                Name, 
                (SELECT Id, 
                        Name, 
                        Contact_Name__c, 
                        Contact__r.Name, 
                        Contact__r.Email 
                    FROM Position__r 
                    WHERE RecordType.Name = 'Collegiate Ministry' 
                    AND Position_Title__c = 'Campus Minister'),  
                (SELECT Address_Line1__c, 
                        Address_Line2__c, 
                        City__c, 
                        State__c, 
                        Zip_Postal_Code__c, 
                        Country__c, 
                        URL__c, 
                        Email__c 
                    FROM Addresses__r 
                    WHERE RecordTypeId IN (:rtLoc.Id, :rtMail.Id)) 
            FROM Account 
            WHERE 
                Name LIKE :term + '%'
                OR
                Position__r.Contact__r.Name LIKE :term + '%'
                OR
                Addresses__r.City__c LIKE :term + '%' 
                OR
                Addresses__r.State__c LIKE :term + '%'

Attribution to: fourq

Possible Suggestion/Solution #1

If the relationship name is Addresses__r, then in your Where clause it should probably read

Id in (select Account__c from Address__c where City__c LIKE :term +'%')
OR
Id in (select Account__c from Address__c where  State__c LIKE :term + '%')

Attribution to: techtrekker

Possible Suggestion/Solution #2

based on the select part of your query it appears that addresses is a child of account, in which case, your where clause don't make any sense. you probably want some kind of semi-join type query, e.g.

select .... from account where id in (select accountId__c from address where city__c like :term)

Attribution to: superfell

Possible Suggestion/Solution #3

In your schema, it looks like you have one Account, lookup to position and related list (many) Addresses.

In your final WHERE clause you are mentioning a related list of addresses ( Addresses_r ) , since this syntax (_r) represents a list, you are building an invalid field path. Basically, this list does not contain a field, it contains records which each may have a City field.

That is why you got the error.

take a look at the semi-join syntax: http://developer.force.com/cookbook/recipe/writing-shorter-queries-using-outer-joins

Attribution to: VNE_Hess
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/5145