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.
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?