function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Westec Intelligent Surv.Westec Intelligent Surv. 

Query for all Notes And Attachments directly or indirectly related to an Account

I need a SOQL query that will retrieve a result set similar to what you get in the Notes And Attachments section of an Account. This list includes not only all Notes And Attachments with the Account itself as the parent, but also for all Cases, Opportunities and/or Contacts related to the Account. With the NoteAndAttachment entity not queryable directly, and with no UNION clause available, I don't even know where to begin writing a single all-includive query.

Best Answer chosen by Admin (Salesforce Developers) 
Damien_Damien_

That means you will just have to do a few separate queries to build a list of IDs.

 

List<id> ids = new List<id>();

for(Account curr:[SELECT ID FROM Account where id =: accountID])

  ids.add(curr.id);

 

for (Opportunies curr: [SELECT ID FROM Opportunities WHERE account =: accountID])

  ids.add(curr.id);

 

...

 

List<Attachments> attachments = [SELECT name FROM Attachments WHERE ParentID IN: ids];

 

Does pretty much the same thing, just requires extra code.

All Answers

Damien_Damien_

[SELECT name FROM Attachments WHERE ParentID =: accountid OR ParentID IN (SELECT ID FROM Case WHERE account =: accountID) OR ParentID IN (SELECT ID FROM Opportunities WHERE account =: accountid) OR ParentID IN (SELECT ID FROM Contacts WHERE account =: accountID)]

 

This should do the trick.  I wrote it off the top of my head so there could be mistakes, but basically this uses Saleforce's pseudo joins.  If you need a reference search developerforce.com for 'SOQL joins'.  You should find sufficient references here to correct any mistakes if I made any in my code.

Westec Intelligent Surv.Westec Intelligent Surv.

 


Damien_ wrote:

[SELECT name FROM Attachments WHERE ParentID =: accountid OR ParentID IN (SELECT ID FROM Case WHERE account =: accountID) OR ParentID IN (SELECT ID FROM Opportunities WHERE account =: accountid) OR ParentID IN (SELECT ID FROM Contacts WHERE account =: accountID)]

 

This should do the trick.  I wrote it off the top of my head so there could be mistakes, but basically this uses Saleforce's pseudo joins.  If you need a reference search developerforce.com for 'SOQL joins'.  You should find sufficient references here to correct any mistakes if I made any in my code.


I'm actually using the API, and it says that only two semi-join sub-selects are allowed per query. I don't know if this is also a limitation in Apex but it doesn't work for me. You also can't use a semi-join with the OR operator.

 

forecast_is_cloudyforecast_is_cloudy

There is no way to perform a single SOQL query to get the result set that you've described (both Notes and Attachments for the Account and all children records). You'll have to issue multiple SOQL queries. Is there a particular reason for not being able to do that?

Damien_Damien_

That means you will just have to do a few separate queries to build a list of IDs.

 

List<id> ids = new List<id>();

for(Account curr:[SELECT ID FROM Account where id =: accountID])

  ids.add(curr.id);

 

for (Opportunies curr: [SELECT ID FROM Opportunities WHERE account =: accountID])

  ids.add(curr.id);

 

...

 

List<Attachments> attachments = [SELECT name FROM Attachments WHERE ParentID IN: ids];

 

Does pretty much the same thing, just requires extra code.

This was selected as the best answer
forecast_is_cloudyforecast_is_cloudy

You could get all the child records for the Account in 1 SOQL query as such:

 

SELECT ID, (select if from Opportunities), (Select id from Contacts), (Select id from Cases) FROM Account where id =: accountID

 

However you'd still need to perform seperate queries on the Attachment and Note objects with the set of Ids gathered from the above query. 

 

TexasRobTexasRob

I had a related need and thought that I would share. In my case, I needed all attachments that were related to any account of a particular type. I wanted the resulting ID for both the attachment (Id) as well as the account (ParentID) from the Attachment object.

 

This seemed to do the job:

 

Select Id, ParentId FROM Attachment WHERE ParentId IN (SELECT Id FROM Account WHERE Type = 'Customer - Direct').

 

 

ibtesamibtesam

Attachments are not queryable..what to do? :( when i write query for attachments it returns nothing.

how to make it queryable??

Damien_Damien_

@ibtesam

 

I'd say 1 of 2 things are true which is causing it to return you nothing.

 

1) The querying user does not have access to the Attachments

2) There are no attachments to query for.

Christian TJuul MikkelsenChristian TJuul Mikkelsen
Hi ibtesam the correct object name is Attachment with the s. 
Andre Baxter 7Andre Baxter 7
I'm having the same issue as ibtesam.  My sandbox has two attachments in it on an Opportunity record at the very least.  However, when I use the query for all atachments "select id, name from Attachment" in the query editor, zero rows are returned.