Query a Collection Across Multiple Fields

Question

I'm aware of this and this posts. They very well explains how to bulkify LIKE queries. But I have to query on multiple fields.

Let say I have in input a list of account names:

1. Salvator Dali
2. Claude Monet

And alredy in SF the capitalized accounts:

3. SALVATOR DALI
4. CLAUDE MONET

but also one particular case that I don't want inside the query result:

5. CLAUDE DALI

How to write a query that, given a list with 1 and 2 gives me the results 3 and 4 but not the 5?

Thanks.

EDIT

I have to query on Firstname and Lastname on Person Account objects. (in the case 1 the Firstname will be Salvator and Lastname will be Dali) and in input I will have two List: one for firstnames and one for lastnames

Answer 1

You didn't specify which other fields you want to search on, but for your simple use case, the following will work:

Set<String> names = new Set<String> { 'SALVADOR DALI', 'CLAUDE MONET' };
List<Account> matches = [SELECT Name FROM Account WHERE Name IN :names];
//Finds Salvador Dali and Claude Monet
//Ignores Claude Dali

EDIT

Okay so I understand the problem better now, you have two potential approaches, either you can find all exact matches by merging in clauses like (FirstName='Salvador' AND LastName='Dali') with an OR join, or you can query where the FirstName and LastName are in your collections and do some post-processing. I prefer the latter especially because it does not require any dynamic SOQL.

Collections Approach:

Map<String, String> firstToLast = new Map<String, String>
{
    'SALVADOR' => 'DALI',
    'CLAUDE' => 'MONET'
};
List<Account> matches = new List<Account>();
for (Account person : [
    SELECT FirstName, LastName FROM Account
    WHERE FirstName IN :firstToLast.keySet()
    AND LastName IN :firstToLast.values()
])
{
    if (firstToLast.get(person.FirstName) == person.LastName)
        matches.add(person);
}

Dynamic SOQL Approach:

Map<String, String> firstToLast; // same as above
List<String> nameClauses = new List<String>();
for (String firstName : firstToLast.keySet())
{
    String nameClause = '(FirstName=\'' + firstName + '\'' +
        ' AND LastName = \'' + firstToLast.get(firstName) + '\')'
    nameClauses.add(nameClause);
}
String namesClause = String.join(nameClauses, ' OR ');
String soql = 'SELECT FirstName, LastName FROM Account WHERE ' + namesClause;
List<Account> matches = Database.query(soql);