+ Start a Discussion
ColealarsColealars 

Filtering master records from child in SOQL

I have a field End_customerId__c on Opportunity which is a lookup to Account.  I've named the relationship End_Cust_Lookup.   I'm trying to get back a list of Opportunities where the accountnumber on the related account record is null. 

 

When I try this query:

 

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

 

I get an error Unable to open Schema Browser. Malformed_Query - end_cust_lookup__r.accountnumber From opportunity where end_cust_lookup__r.accountnumber.  No viable alternative at character ''.


I also tried the following but that doesn't filter out the non null records because I guess you can't filter master records from child this way/

 

Select  (Select Opp_Uid__c From End_Cust_Lookup__r where Opp_uid__c <> NULL )  From Account a where  a.accountnumber = NULL
 

 

Best Answer chosen by Admin (Salesforce Developers) 
Starz26Starz26

I have a custom object where Opportunity_Name__c is the lookup relationship API Name for the lookup file to Opportunities on the custom object.

 

The following query will return a list of Implementation_Hand_Off records with an Opportunity Name Like "%test"

 

Select ID, Name, Opportunity_Name__r.name From Implementation_Hand_Off__c WHERE Opportunity_Name__r.name LIKE '%test%'

 

So, I think you need to recheck your query to ensure you are using the API name for the field, I bet it should be:

 

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

 

 

All Answers

Starz26Starz26

Try

 

Select accountnumber, (Select Opp_Uid__c From Opportunities) From Account WHERE accountnumber = NULL

 

see below

 

Starz26Starz26

I have a custom object where Opportunity_Name__c is the lookup relationship API Name for the lookup file to Opportunities on the custom object.

 

The following query will return a list of Implementation_Hand_Off records with an Opportunity Name Like "%test"

 

Select ID, Name, Opportunity_Name__r.name From Implementation_Hand_Off__c WHERE Opportunity_Name__r.name LIKE '%test%'

 

So, I think you need to recheck your query to ensure you are using the API name for the field, I bet it should be:

 

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

 

 

This was selected as the best answer
ColealarsColealars

Okay thanks I got it working.