+ Start a Discussion

Help with filtering on relationship query

I'm trying to filter the result set of the following query to only return records where Opp_Uid__c is not null.


Select  (Select Opp_Uid__c From End_Cust_Lookup__r where stage_code_num__c < '50' )  From Account a where  a.accountnumber = NULL


When I add the where End_Cust_Lookup__r.Opp_uid__c <> NULL at the end of the statement above I get an error that it didn't understand relationship Ennd_Cust_Lookup__r in field path.


When I add Opp_uid__c <> NULL to the where clause in the subquery I get the same results set as I do without this additional where.


Maybe I'm going about this all wrong.  I have a lookup field end_customerID__c on the opportunity object which does a lookup to account.  I want to return a list of opportunities where the account.accountnumber is null on the end customer listed on the Opp.






This is parent -to- child query and you cannot filter master object on the basis of child object's fields.

You should use child -to-parent query. Try this one -


[Select Opp_Uid__c,end_customerID__r.accountnumber From opportunity  where  end_customerID__r.accountnumber = NULL AND Opp_Uid__c <> NULL];



Hope it would help u.



I gave that a try and I got error Unable to open Schema Browser.  Malformed Query It didn't like End_Cust_Lookup__r.  It says no viable alternative at character ''


Select Opp_Uid__c, End_Cust_Lookup__r.accountnumber From opportunity  where  End_Cust_Lookup__r.accountnumber = NULL AND Opp_Uid__c <> NULL


Is the End_Cust_Lookup__r a lookup field on Opportunity that looks up Account?


The lookup field it End_CustomerID__c but I name the relationship end_cust_lookup so I thought you made a typo and change it to end_cust_lookup__r.