Trigger SOQL on Parent/Child/Optional Child situation (Too many SOQL Queries)

Question

The following is a part of an AFTER trigger that I have written.

It works fine - on 1 record... but when you get a few more than 1 I am getting the Too Many SOQL Queries error.

• The first select (lstFA) is on the parent object (Fixed_Asset__c)

• The second select (lstHIST) is a child object of Fixed_Asset__c and I want to only return 0 or 1 records per Fixed_Asset__c - the last one of a certain type (or none at all for that Asset).

• Then based on all of that in lstHIST there is a field Assigned_Key__c and depending on the value in that field I have to query either an Account, Contact, Opportunity, User or Fixed_Asset_Holder__c.

The 'for' in this trigger is a ways farther down - but there is no more SOQL after what I show below.

Can anyone assist me in re-working these queries so the trigger works for more than 1 update at a time?

if (trigger.isUpdate){
       lstFA = [select Id, Name, AcctSol__Internal_Conversion__c, AcctSol__Transfer_Date__c, AcctSol__Assigned_Date__c,
         AcctSol__Account__c,AcctSol__Assigned_Address__c,AcctSol__Assigned__c,AcctSol__Assigned_City__c,
         AcctSol__Contact__c,AcctSol__Assigned_Country__c,AcctSol__DepartmentInfo__c,AcctSol__Assigned_Email__c,
         AcctSol__Fixed_Asset_Holder__c,AcctSol__Assigned_Mobile__c,AcctSol__Assigned_Name__c,AcctSol__Opportunity__c,
         AcctSol__Assigned_Other_Phone__c,AcctSol__Assigned_Phone__c,AcctSol__Assigned_State_Province__c,
         AcctSol__Assigned_Postal_Code__c,
         AcctSol__Loan_Account__c,AcctSol__Loan_Address__c,AcctSol__Loan_Start_Date__c,AcctSol__Loan_Type__c,
         AcctSol__Loan_City__c,AcctSol__Loan_Contact__c,AcctSol__Loan_DepartmentInfo__c,AcctSol__Loan_Country__c,
         AcctSol__Loan_Email__c,AcctSol__Loan_Estimated_Return_Date__c,AcctSol__Loan_Mobile__c,AcctSol__Loan_Name__c,
         AcctSol__Loan_Opportunity__c,AcctSol__Loan_Other_Phone__c,AcctSol__Loan_Phone__c,AcctSol__Loan_State_Province__c,
         AcctSol__Loan_User__c,AcctSol__Loan_Zip_Postal_Code__c,AcctSol__QR_Code__c,AcctSol__QR_Code_Asset_ID__c,
         AcctSol__Remaining_Orginal_Cost__c,AcctSol__Original_Cost__c,AcctSol__Insurance_Policy_LU__c,
         from AcctSol__Fixed_Asset__c where Id = :Trigger.New];

       AcctSol__Fixed_Asset__c OLDFA = Trigger.OldMap.get(lstFA[0].Id);

       if(lstFA[0].AcctSol__Internal_Conversion__c == '18U') 
       {
         lstHIST = 
           [SELECT Id,AcctSol__Address__c,AcctSol__Assigned_Key__c,AcctSol__Assigned_Type__c,AcctSol__Current_Date__c,
           AcctSol__Description__c,AcctSol__Email__c,AcctSol__Fixed_Asset__c,AcctSol__Loan_Date__c,AcctSol__Name1__c,
           AcctSol__New_User__c,AcctSol__Phone_Number__c,AcctSol__User__c,AcctSol__Return_Date__c,AcctSol__Starting_Date__c
           FROM AcctSol__History_Tracking_Fixed_Asset__c 
           WHERE AcctSol__Fixed_Asset__c=:Trigger.New and AcctSol__Loan_Date__c <> null 
           ORDER BY Name DESC LIMIT 1]; 

           if(lstHIST.size() > 0)
           {
             if(lstHIST[0].AcctSol__Assigned_Key__c <> null)
             {
                 if (lstHIST[0].AcctSol__Assigned_Type__c == 'Account')
                 {
                   lstAcc = [select id,Name,Phone,BillingCity,BillingCountry ,BillingPostalCode,BillingState,
                            BillingStreet,Department__c from Account where Account.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
                 }
                 else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Contact')     
                 {
                   lstCon = [select id,Name,Phone,Email,OtherPhone,MobilePhone,Department,MailingCity,MailingCountry,
                            MailingPostalCode,MailingState,MailingStreet 
                            from Contact where Contact.Name = :lstHIST[0].AcctSol__Assigned_Key__c];
                 }
                 else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Asset Holder')
                 {
                   lstFAH = [select id,Name,Phone__c,Email__c,Other_Phone__c,Mobile__c,Department__c,City__c,Country__c,
                           Postal_Code__c,State_Province__c,Address__c 
                           from AcctSol__Fixed_Asset_Holder__c where AcctSol__Fixed_Asset_Holder__c.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
                 }
                 else if (lstHIST[0].AcctSol__Assigned_Type__c == 'Opportunity')
                 {
                   lstOpp = [select id,Name from Opportunity 
                            where Opportunity.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
                 }        
                 else if (lstHIST[0].AcctSol__Assigned_Type__c == 'User')                                        
                     lstUsr = [select id,Name,MobilePhone,Email,Phone,Street,State,PostalCode,Country,City,
                               Department from User where User.Name=:lstHIST[0].AcctSol__Assigned_Key__c];
            }
           }

Answer 1

    List<Id> assignedKeys = new List<Id>{};
    Map<Id, Account> assignedObjects = new Map<Id, sObject>{};
    if (trigger.isUpdate){
    for(Fixed_Asset__c lstFA : trigger.new){
    //collect all the assigned keys in a list
    if(Trigger.OldMap.get(lstFA[0].Id).AcctSol__Internal_Conversion__c == '18U')
    assignedKeys.add(lstFA.AcctSol__Assigned_Type__c);


//select all the accounts which have name in the assigned keys collection above
    for(Account acc : [select id,Name,Phone,BillingCity,BillingCountry ,BillingPostalCode,BillingState,BillingStreet,Department__c from Account where Account.Name IN :assignedKeys])
assignedObjects.put(acc.Name, acc);

 for (Contact con : [select id, Name, Phone, Email, OtherPhone, MobilePhone, Department,MailingCity,MailingCountry,MailingPostalCode,MailingState,MailingStreet from Contact where Contact.Name IN :lstHIST[0].AcctSol__Assigned_Key__c])
assignedObjects.put(con.Name, con);

... ditto for Opportunities and Asset Holders

}

//Now assignedObjects contains a Map of the key and Object (Acc, Con, ...)
To access Account, just type cast to account
lstAcc  = (Account)assignedObjects.get(Key);