You need to sign in to do that
Don't have an account?
Rahul63
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
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
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?
All Answers
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.
Query the children from Parent
--
Thanks,
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
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
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
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?