How to retrieve only the lead details which doesn't have any non-converted duplicate entries in SOQL query

Question

I have knowledge on SQL but new to Salesforce and SOQL. I am facing issue with the below scenario in writing the SOQL query to fetch the details.

Scenario:

I have a Lead Table like below from where want to retrieve only the lead details which doesn't have any non-converted duplicate entries. (eg; only the yellow highlighted rows) Importantly I don't want to return the row even if it is converted because it has a duplicate non- converted entry at row 4.

Equivalent SQL query: SELECT FirstName, LastName, Email, Phone, MobilePhone from Lead WHERE FirstName NOT IN (SELECT FirstName FROM Lead WHERE IsConverted = false)

SOQL Error: MALFORMED_QUERY: NOT IN (SELECT FirstName FROM Lead WHERE IsConverted = false) ^ ERROR at Row:1:Column:111 The inner and outer selects should not be on the same object type

So What are the right/possible ways in the SOQL query?

Answer 1

SOQL does not allow such query, so you need to separate it into 2.

1) Gather leads and store their first names into Set.

List<Lead> leads = [SELECT FirstName FROM Lead WHERE IsConverted = false];
Set<String> leadsNames = new Set<String>();
for (Lead lead_i : leads) {
    leadsNames.add(lead_i.FirstName);
}

2) Now you can query:

SELECT FirstName, LastName, Email, Phone, MobilePhone from Lead WHERE FirstName NOT IN :leadsNames