+ Start a Discussion
Vidya BhandaryVidya Bhandary 

Parent SOQL query - return non null child objects

I want to get records for which the child records are not null. I tried to use the resolution provided at the link mentioned below but it did not help. I tried the query with "!= null" as well as "!=''".

 https://developer.salesforce.com/forums/?id=906F00000008lxBIAQ .

I have been breaking my head over it ... hope someone else can help with this.

The query -

(Select Id, FieldA, FieldB, FieldC From Child__r where FieldA IN
('Value1', 'Value2','Value3')  ) ,

o.Parent_Date_Time__c From Parent__c o

where o.Id IN (Select Parent__c from Child__c where Parent__c != '') AND

o.LastModifiedDate >= 2013-05-01T23:59:59Z AND
o.Parent_Date_Time__c = LAST_N_DAYS: 15
The way I would approach is 2 have 2 SOQL queries.Let's Assume Account(Parent) and Contact(Child).

Then,the code would be
//Query all contact whicch have associated Account
List<Contact> conList=[select Id,AccountId from Contact where AccountId!=null]

List<Id> accIds =  new List<Id>();

//Populating all the account Ids which have related contacts
for(Contact con:conList)
 //Now query on Account to get the values
 List<Account> accList =[select id,Name from Account where id in:accIds];

Hope this helps!!
Use the solution explained in the below post

Vidya BhandaryVidya Bhandary
Hi Vinit,

Thank you for your answer. Can it not be done with one SOQL ? My current application needs it in one SOQL if possible.

Vidya BhandaryVidya Bhandary
Hi Ramu,

Thank you for your response. I had looked at this link but it did not help.
Vidya BhandaryVidya Bhandary

The problem was that the rows returned for which I thought the child did not exist was incorrect and yet it showed it up as blank. So on adding a where clause to the last part of the query I was able to get only those rows I wanted and the ones I thought were null/blank did not turn up. [ Although I would have thought on checking in workbench it would have showed up with some Id !!!! The blank / no data there confused me  ]

So the query in the child object

(Select Parent__c from Child__c where Parent__c != '') now includes where FieldA IN
('Value1', 'Value2','Value3') 

Thanks for the replies !!!

You can try something like this also to sum up in one query.

List <Account> accList = [select id from Account where id in(select AccountId from contact)];
Anurag DathareyaAnurag Dathareya

Selecting parent record whose child records are not null, 

so please be aware the subquery that you use in the main select statement, if any condition exists in that subquery dont forget to add it in your second subquery

For eg: 

Select Id, (Select Id, CustomField__c, Name.... FROM <ChildObject> WHERE <CONDITION A>) FROM <ParentObject> WHERE Id IN (SELECT ParentLookUpField__c FROM <ChildObject> WHERE <CONDITION A>)

The reason I boldified(not a word, lol) the second "<ChildObject>" is use the normal api name (__c or the standard api name)