4

I am trying to get a list of all Accounts with their related Cases. There is a lookup on the Case object to Account (AccountId). Right now I have to do two queries:

    List<Account> stores = [
        SELECT Account.Name
        FROM Account 
        WHERE Account.Id IN (SELECT Case.accountId FROM Case)];

List<Case> predictions = [
    SELECT Id, AccountId, CaseNumber
    FROM Case
    WHERE AccountId = :stores.get(i)
];

Is there anyway to do a nested SELECT in the Accounts query? I'm horrible with inner/outer joins.

4

You can combine your two queries into one like:

SELECT Name, (SELECT Id, AccountId, CaseNumber FROM Cases) FROM Account WHERE Id IN (SELECT AccountId FROM Case)

Alternative would be to query for the Cases and related Account data like:

SELECT Id, AccountId, Account.Name, CaseNumber FROM Case WHERE AccountId != ''

The Force.com SOQL and SOSL Reference pdf, found here, is a great resource!

Adding an image from the developer console from the first query:
Developer Console Query

  • When trying the first option, I don't see the Case information in my response (Id, AccountId, CaseNumber). All I get back is the Name (account) and Id (account). – Austin Nelson Sep 1 '15 at 12:55
  • @AustinNelson - Ok weird...I see the same thing BUT if you add a limit statement to the subquery From Cases Limit 10 it works....Or if you add a selective Where Clause – Eric Sep 1 '15 at 13:22
  • Sorry, I edited the wrong post – Eric Sep 1 '15 at 13:29
  • [SELECT Name, (SELECT Id, AccountId, CaseNumber From Cases LIMIT 10) FROM Account WHERE Id IN (SELECT AccountId FROM Case)] Still doesn't return any Cases. I've even tried on a specific Account: WHERE Id = '' And couldn't get any Cases back – Austin Nelson Sep 1 '15 at 13:33
  • @AustinNelson attaching an image of the query from my dev org. Assuming you have data where cases are related to the account? What do you get back on the second query? – Jenny B Sep 1 '15 at 14:26
6

This should do it:

If you have a list of Account IDs say Set<ID> acctIDs

[Select Name, ID, (Select ID, CaseNumber From Cases) From Account Where ID = :acctIDs];

Update: As of 9-2-2015 The above always returns 0 records for cases. BUT if you add a LIMIT clause records are returned OR if you add a selective where clause, records are returned. Not sure if it is a bug or by design

  • Thanks for the suggestion! Unfortunately I still don't get back any Case information, just Account information. – Austin Nelson Sep 1 '15 at 13:16
  • @AustinNelson - Ok weird...I see the same thing BUT if you add a limit statement to the subquery From Cases Limit 10 it works....Or if you add a selective Where Clause – Eric Sep 1 '15 at 13:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.