+ Start a Discussion
rodrigorrodrigor 

SOQL: How to find parent records with no associated child?

Is there a way to get a list of Account records (parent) with no child records associated (Opportunity)  via SOQL?

I am trying to avoid doinf this in Apex, like getting the AccountId values from Opportunity and then query accounts where id not in this list.

 

Is it possible to get this list through SQOL?


Here is my query (I need only the accounts that have no associated Opportunity records):

 

SELECT a.Id, a.Name, (Select id from Opportunities) from Account a order by a.Name

 

Thanks,

Rodrigo

Best Answer chosen by Admin (Salesforce Developers) 
Daniel Zeidler (DZ)Daniel Zeidler (DZ)
SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity)

 

All Answers

Daniel Zeidler (DZ)Daniel Zeidler (DZ)
SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity)

 

This was selected as the best answer
ryanjuptonryanjupton

There's an example about midway down this blog post. It also might help more in general to understand how relationship queries work on our platform.

 

http://blogs.developerforce.com/developer-relations/2013/05/basic-soql-relationship-queries.html

Bhawani SharmaBhawani Sharma
Best option would be, create a rollup field on account with Count(Opportunity) function. And then fire a query like
Select Id from Account where Rollup_Field__c = 0
rodrigorrodrigor
Thank you guys so much for your help!
Ankit Gupta SFDCLearnerAnkit Gupta SFDCLearner
Just want to fetch no of parents records who have no child records, I need it's SOQL. Below soql is running for standard object SELECT Id, Name FROM Account WHERE Id NOT IN (SELECT AccountId FROM Opportunity) I need it for custom object. Please try to solve.