Nested SOQL query?

Question

so, the following query is pretty self explanatory:

SELECT p.Accountid, p.Name, (select name FROM Account where id = p.Accountid) FROM Contact p order by accountid'

is there any way to do this in a single query? I'm looking to avoid this:

map accounts = new Map<String, String>();

for(Account a :[SELECT name, id FROM account])
{
    accounts.add(id, name);
}

map mapc = new Map<String, Contact>();

for(Contact l: [SELECT accountid, name from contact order by accountid])
{
    mapc.add(accounts[l.accountid], l);
}

(not debugged, but you get the idea)

and I cannot make a custom relationship to account, because my org admin doesn't want me to.

Thanks!

Answer 1

// create a map of Contacts by Account Ids
Map<Id, List<Contact>> ContactMap = new Map<Id, List<Contact>>();

// query (where/and conditions can be added as needed)
List<Account> Accounts = [Select Id, Name, (Select Id, Name From Contacts) From Account];

// loop through all retrieved Accounts 
for(Account a : Accounts)
{
   List<Contact> Contacts = a.getSObjects('Contacts'); // get all Contacts for the Account
   ContactMap.put(a.Id, Contacts); // put the Account Contacts in a map by Account Id
}

Answer 2

You can do this by following the relationship in the query, e.g.

select accountId, account.Name, id from contact order by accountId

There are more examples in the SOQL docs

Answer 3

You can use a nested for loop to iterate through subquery results:

List<Account> accList = new List<Account>([
    SELECT
        Name,
        Id,
        (
            select
                Id  
            from
                Contacts
        )
    FROM
        Account
    ORDER BY
        Id
]);

Map<String, Contact> conMap = new Map<String, Contact>();

for (Account acc :accList)
{
    for (Contact con :acc.Contacts)
    {
        conMap.put(con.Id, con);
    }
}