+ Start a Discussion
Rahul63Rahul63 

SOQL: How to find parent custom object records with no associated child custom object

I need to fetch the list of parent records which are not having child records associated to parent
Parent Object : Building__c
Child Object : InstallationInstallation_Address__c

I have tried the query in workbench : Select Id,Name  from Building__c WHERE Id NOT IN (Select Building__r.Id from Installation_Address__c) 
But i couldn't able to execute the query due to below error,

MALFORMED_QUERY: 
Building__c WHERE Id NOT IN (Select Building__r.Id from Installation_Address__c)
^
ERROR at Row:1:Column:90
The inner select field 'Building__r.Id' cannot have more than one level of relationships

Can you please help to fix above query.
Thanks in advance.

//Rahul
Best Answer chosen by Rahul63
Andy on CloudAndy on Cloud
Hi Rahul,

Why do you need to use Building__r.Id in your sub-query?  As the lookup API name is the same as __r.Id, so have you tried this?
 
Select Id,Name  from Building__c WHERE Id NOT IN (Select Building__c from Installation_Address__c)

All Answers

Prashant Pandey07Prashant Pandey07
Hi Rahul,

Do the parent and child object are related together. if yes then you need to make sure you are picking the right api name.

Query the Parent from the child.
 
select id from Building__c from InstallationInstallation_Address__c

Query the children from Parent
 
select id, (select id  from InstallationInstallation_Address) from building__c

--
Thanks,
Prashant
Rahul63Rahul63
Hi Prashant,
I guess you didn't understood the requirment, already i have tried similar kind of query but it's not fetching correct buildings record
select id, (select id  from InstallationInstallation_Address__c where id = '') from building__c

//Rahul
Prashant Pandey07Prashant Pandey07
Hi Rahul,

select id, (select id  from InstallationInstallation_Address__c where id = '') from building__c
The above query will not work..Instead of InstallationInstallation_Address__c field, you need to give the right child relationship field name..

Let me know if I am missing anything.
--
Thanks,
Prashant
Ajay K DubediAjay K Dubedi
Hi Rahul,
Please try this code on executeAnonymous window.

List<Installation_Address__c> InstallationList = new List<Installation_Address__c>();
acList = [Select Id,Name,Building__c from Installation_Address__c];
System.debug('child=='+InstallationList);
Set<Id> IdCollect = new Set<Id>();
for(Installation_Address__c insAdd:InstallationList){
    if(insAdd.Building__c!=Null){
        IdCollect.add(insAdd.Building__c);
    }
}

list <Building__c>  BuildList = new List<Building__c>();
BuildList = [Select id, Name from Building__c Where Id Not IN: IdCollect];
System.debug('Parent=='+BuildList);


Please let me know if you have any query.

Please mark it as best Answer if you find it helpful.

Thank You
Ajay Dubedi
Andy on CloudAndy on Cloud
Hi Rahul,

Why do you need to use Building__r.Id in your sub-query?  As the lookup API name is the same as __r.Id, so have you tried this?
 
Select Id,Name  from Building__c WHERE Id NOT IN (Select Building__c from Installation_Address__c)
This was selected as the best answer