10

Let's say I have object Parent that has a child relationship Child__r. I'd like to select only those parents that have children through SOQL. How can I do that?

13

You can use a subquery to filter out Parent Records that don't have a related child,

eg

Select Id, Name from Account where Id In (Select AccountId from Contact) 

In your case

Select Id, Name from Parent__c where Id In (Select ParentId__c from Child__c)
| improve this answer | |
  • Is it possible to run into limits with the inner query? (Select AccountId from Contact) – Detached Dec 14 '16 at 11:09
2

You can use a subquery in your where clause to only retrieve parents with children:

select Name, Parent_Field_1__c, Parent_Field_2__c
from Parent__c
where Id in (select Parent__c from Child_Object__c)

If you'd like to actually retrieve the child relationship records as well, you can do this in the same query by adding a subquery to your select clause:

select Name, Parent_Field_1__c, Parent_Field_2__c,
    (select Child_Field_1__c from Child__r)
from Parent__c
where Id in (select Parent__c from Child_Object__c)
| improve this answer | |
1

If it is a master detail, you could add a roll up summary field to count children, the use a filter condition of where child counter > 0.

| improve this answer | |
1

You could use a SOQL query based on the child record. If the parent is guaranteed to be populated, like in a Master-Detail relationship, you could use something like this:

SELECT Parent__r.Id, Parent__r.Field1, Parent__r.Field2 
FROM Child__c 
WHERE Parent__r.FieldToFilter = SomeCondition
| improve this answer | |
  • Mike, this is an outside-the-box way to approach this! I'm not saying I'd actually do this, but it's clever :) – zachelrath Jun 27 '13 at 11:37
  • This was what I was thinking... I guess the downside is that if you have you will be selecting the number of rows equal to the number of children rather than the number of parents, which depending on the data model could be a lot more than you want and a lot of deduplication. – Doug B Jun 27 '13 at 11:37
0

Here is another option: Useful when needing a dynamic approach

dateTime dT = dateTime.now().addHours(-1);
list<Account> accs = database.query('SELECT id, name FROM Account WHERE Id IN (SELECT Custom_Account_Field__c FROM Child__c WHERE Custom_Filter_Field__c = \'filter\' AND Custom_Date__c > :dT) LIMIT 50'); 
system.debug(accs);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.