11

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?

0

6 Answers 6

15

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)
1
  • Is it possible to run into limits with the inner query? (Select AccountId from Contact)
    – noBillSide
    Dec 14, 2016 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)
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.

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
2
  • 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, 2013 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, 2013 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);
-3

select id,name,(select id,name from Products__r where Product_Category__c NOT IN (select id from Product_Category__c )) from Product_Category__c

Parent Object - Product_Category__c Child Object - Products__r

2
  • As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Nov 30, 2021 at 11:07
  • This appears to be the opposite of what the question is asking for, and is lacking in detail.
    – Derek F
    Nov 30, 2021 at 12:21

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .