How to populate field with data 2 levels down?

Question

We have a scenario of accounts who have contacts where some have one or more records in a custom object named members__c but not all contacts have a member__c record. member__c object has a lookup relationship with the contact object.

How to select all contacts who have at least one member__c record and insert their first and last name in a field named "member__c" on the account object?

Answer 1

Perhaps aggregate querying will help. Something like below.

List<AggregateResult> aggregates = [
    SELECT
        Contact__r.AccountId accountId,
        Contact__r.FirstName first,
        Contact__r.LastName last
    FROM Members__c WHERE Contact__r.AccountId = :accountid
    GROUP BY Contact__r.AccountId, Contact__r.FirstName, Contact__r.LastName
];

Map<Id, Account> parentRecords = new Map<Id, Account>();
for (AggregateResult aggregate : aggregates)
{
    Id parentId = (Id)aggregate.get('accountId');
    String firstName = (String)aggregate.get('first');
    String lastName = (String)aggregate.get('last');
    parentRecords.put(parentId, new Account(
        Id = parentId, Member__c = firstName + ' ' + lastName
    ))
}
update parentRecords.values();

Answer 2

I'd expect your query to look something like the following:

SELECT Id, Name, Member__r, ContactId__r (SELECT Name, AccountId, Member__  
    FROM contacts), (SELECT Id, BOG_Member__c.Name, ContactId, FROM Member__r)
       FROM Account WHERE Account.Name LIKE '%washington%' 

From the above you should be able to create maps of Account.Id to ContactId and ContactId to Member__c.Id