+ Start a Discussion
ColealarsColealars 

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.

 

 

Alok_NagarroAlok_Nagarro

Hi,

 

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.

 

ColealarsColealars

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

Starz26Starz26

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

ColealarsColealars

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.