2

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!

  • Try [select Id, name, (select Id, name from contacts) from account] – techtrekker Jul 2 '13 at 21:20
  • 1
    If you're primary object is the Contact, I like Superfell's answer. If your primarily working with Contacts and need to loop through an Account's Contacts then Matthew's answer will do it. – Mike Chale Aug 7 '13 at 23:54
5
// 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
}
5

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

2

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);
    }
}

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.