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.

2 Answers 2

5

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

7
  • 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). Sep 1, 2015 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, 2015 at 13:22
  • Sorry, I edited the wrong post
    – Eric
    Sep 1, 2015 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 Sep 1, 2015 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, 2015 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

2
  • Thanks for the suggestion! Unfortunately I still don't get back any Case information, just Account information. Sep 1, 2015 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, 2015 at 13:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .