Retrieve a list of Cases from Accounts SOQL

Question

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.

Answer 1

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:

Answer 2

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