Query results from two list related to each other

Suppose I have in my org those Contact records:

1) FirstName = 'Ross', LastName = 'Geller'
2) FirstName = 'Rachel', LastName = 'Green'
3) FirstName = 'Monica', LastName = 'Geller'
4) FirstName = 'Ross', LastName = 'Green'

and somebody, in an apex method, passes me those two lists:

List<String> firstNameList = new List<String>{'Ross', 'Rachel'};
List<String> lastNameList = new List<String>{'Geller', 'Green'};

is there a way to query only the “desired” contacts? (‘Ross Geller’ and ‘Rachel Green’)

If I do:

List<Contact> contactList = [SELECT Id, firstName, LastName from Contact where FirstName in :firstNameList and LastName in :lastNameList];

I get:

Ross Geller
Rachel Green
Ross Green

If I do:

List<Contact> contactList = [SELECT Id, firstName, LastName from Contact where FirstName in :firstNameList or LastName in :lastNameList];

I get:

Ross Geller
Rachel Green
Monica Geller
Ross Green

Answer

1) Create a formula field

FullName__c = FirstName  + " "+ LastName

2) Then in your apex code create a third list:

List<String> fullNameList = new List<String>();
for(Integer i=0;i<firstNameList.size();i++){
    fullNameList.add(firstNameList[i] + ' '+ lastNameList[i]);
}

List<Contact> contactList = [SELECT Id, firstName, LastName from Contact where FullName__c in :fullNameList]

Attribution
Source : Link , Question Author : sfdxdev , Answer Author : Pranay Jaiswal

Leave a Comment