function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
KyriacosKyriacos 

Can you help me with a query

Hi,

 

I want to run a query in Salesforce because I cannot get the result I want from reporting.

 

We have a Custom Object A. Object A is the master of Object B.

 

I want to get a list of all the records in object A that have no related records from Object B. Is that possible?

 

Regards,

 

HighPar

TejTej

Try this query,

 

which gives you list of "A" records that doesn't have related "B" records.

 

SELECT Id, Name, (SELECT AId,Id FROM B where id = null) FROM A

 

Thanks,

 
gotherthanthougotherthanthou

I don't see how that query could possibly work.  That inner query would always return no records as B will always have an ID.  And anway, I don't think that is even an allowed form of syntax for SOQL.  Indeed, when I try it on an example from my dev org:

 

List<Sale__c> sales = [SELECT Id, Name, (SELECT Sale__c,Id FROM Sale_Item__c where id = null) FROM Sale__c];

 

I receive the error:

line 1, column 23: Didn't understand relationship 'Sale_Item__c' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

 

Try this.  There might be a snazzier way to do it but this works (at least for small datasets, you would have to modify/add to it for it to work with large ones):

 

List<B__c> bs = [SELECT AID__c FROM B__c];

Set<ID> ids = new Set<ID>();
for (B__c b : bs)
    ids.add (b.ID);

List<A__c> as = [SELECT ID, Name FROM A__c WHERE ID NOT IN :ids];

 
TejTej

I derived it from the below one:

 

SELECT Id, Name, (SELECT AccountId,Id FROM Contacts where Id = null) FROM Account

 

it works for me n reurning al the accounts that doesnt have related contacts
gotherthanthougotherthanthou

Darned peculiar.

 

Look at code below.  First line works.  Second line is the same error.

 

List<Account> accts = [SELECT Id, Name, (SELECT AccountId,Id FROM Contacts where Id = null) FROM Account];

List<Sale__c> sales = [SELECT Id, Name, (SELECT Sale__c,Id FROM Sale_Item__c where id = null) FROM Sale__c];

 

Tried replacing Sale_Item__c with Sale_r and also Sale_Item__r but no luck.  Interesting.  I think it's because Contacts are related to Accounts, but in my org Sale and Sale Item are a master detail relationship, like what the OP was describing.  Anyway, whatever works.