trigger creating multiple records where I need only 1

Question

I am working on one trigger on lead object, on lead I have a multi select picklist values from which I need to match those multi select picklist values with Account name then if matches I need to create records in affiliation object which is working perfectly now.

The issue is, I can have multiple accounts with same name where each multi select picklist value can match with multiple account names and creating multiple records in affiliation object for each multi select picklist value.

Now instead of creating multiple records for each multi select picklist value I need only one record with any matched Account Id, can anyone help me out in this issue.

My code is below.

Set<String> otherProgramsValueSet = new Set<String>();
for (Lead em : Trigger.new) {
    if(em.Email != null){
        listEmail.add(em.Email);
    }
    if(em.What_is_your_primary_program_of_interest__c != null ){
        listname.add(em.What_is_your_primary_program_of_interest__c);
    }
    if(em.Other_Programs__c != null){
        // this will construct a set of values selected in multiselect. Set will maintain unique values.
        // Multiselect values are semi-colon separated, hence the split function.
        if(em.Other_Programs_of_Interest__c.split(';').size()>0)
        otherProgramsValueSet.add(em.Other_Programs_of_Interest__c.split(';')[0]); 
    }
}

//Query account records with matching names.
//Iterate over this list to create new Affiliation records
List<hed__Affiliation__c> affiliationListToBeInserted = new List<hed__Affiliation__c>();
for(Account acc : [select id from account where name in : otherProgramsValueSet]){
    affiliationListToBeInserted.add(new hed__Affiliation__c(Account__c= acc.Id));
}
insert affiliationListToBeInserted;

Answer 1

otherProgramsValueSet.addAll(em.Other_Programs__c .split(';'));

This is creating problem as you are collecting all values. Instead use:

otherProgramsValueSet.add(em.Other_Programs__c .split(';')[0]);

Then you will get 1st value only. Better to add condition before it:

if(em.Other_Programs__c .split(';').size()>0)
otherProgramsValueSet.add(em.Other_Programs__c .split(';')[0]);

added based on comment

Modify below code:

List<hed__Affiliation__c> affiliationListToBeInserted = new List<hed__Affiliation__c>();
for(Account acc : [select id from account where name in : otherProgramsValueSet]){
    affiliationListToBeInserted.add(new hed__Affiliation__c(Account__c= acc.Id));
}
insert affiliationListToBeInserted;

TO:

List<hed__Affiliation__c> affiliationListToBeInserted = new List<hed__Affiliation__c>();
    Map<String,Id> accNamesToIdsMap = new Map<String,Id>();
    for(Account acc : [select Id,Name from account where name in : otherProgramsValueSet]){
        accNamesToIdsMap.put(acc.Name,acc.Id);
    }
    for(Id accId : accNamesToIdsMap.values()){
        affiliationListToBeInserted.add(new hed__Affiliation__c(Account__c=accId));
    }
    insert affiliationListToBeInserted;