+ 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.
sam karolsam karol
Great opportunity I have seen here https://freevbucksblog.com which give lots of simple solution.
Roxely MaiasRoxely Maias
Here is my query (I need only the accounts that have no associated Opportunity records), see: https://mrjhonnway.medium.com/ and https://mrjhonnway.medium.com/what-is-link-juice-c3ca078effcd
anna smithanna smith
The education is necessary because through this you become an expert and get acknowledge about different important aspects. DumpsExpert products contain information that has been extracted from authentic and reliable sources. https://www.dumpsexpert.com/SY0-601-Exam-Dumps.html dumps pdf offers you updated knowledge about all the contents of the syllabus and will enable you to pass it with flying colors. If you need to go through the entire syllabus and learn each and every topic, choose our Study Guides that equip you with the best knowledge.
vfdshn htrgutfvfdshn htrgutf
We ultimately had to open a case with SF once more to get assist to change the fame from "Provisioning" to "Awaiting Activation", because it became caught on "Provisioning" Status .  They changed it for us and we have been capable of lively it with website (https://yoursilverlab.com/catahoula-pit-mix/).
Civil 9to5Civil 9to5
Truss joint method (https://9to5civil.com/zero-force-member/) : Is there a way to get a list of Account records (parent) with no child records
house of outsourcinghouse of outsourcing
  We are extremely concerned with coding and billing procedures, and we work with our cardiologist to help them maximize their earnings while still complying with regulatory requirements. We handle daily billing and swiftly reply to claim denials and rejections. We set up charting and coding to comply with the annual payer modifications that you specify. 
https://houseofoutsourcing.com/speciality/cardiology-billing-services/