The SUBQUERY below works on its own; in our org, 72 email addresses are returned with a count of id greater than 1
I would like to create a query that returns the Contact.ID value (and other fields) for each of the 72 multiple/duplicate contact.email valuesSELECT email FROM contact GROUP BY email HAVING COUNT(id) > 1
This query below does NOT work... "unknown error parsing query"
Any suggestions? I have tried variations on this and searched the forums without success.Thank you. Enjoy today!TenaSELECT email, Id FROM contact WHERE email IN (SELECT email FROM contact GROUP BY email HAVING COUNT(id) > 1)
Hi,SOQL uses only the relationships of your data model for joining the objects.You cannot join objects using any columns like in SQL having about the same type.Only the columns involved in relationships (lookup/master-detail) can be used.Semi-join (left inner join) with the same object is not authorized in SOQL.In fact, you quickly need some Apex code and List/Set/Map/AggregateResult for storing the results of intermediate queries.So here, you have to store in a set all the names having count > 1 and then you can use a second query with a clause "where name in :mySet"
You can launch the code from the developer console as an anonymous code (CTRL + E)RegardsAlainAggregateResult[] myResult = [SELECT email, count(id) myCount FROM contact GROUP BY email HAVING COUNT(email) > 1];
Set <String> mySet = new Set<String>();
for (AggregateResult agg :myResult) {
String email = (String)agg.get('email');
integer myCount = (integer)agg.get('myCount');
system.debug('email: ' + email + ' count:' + myCount);
mySet.add(email);
}
List<Contact> myList = [select Id, email,firstname,lastname from Contact where email in :mySet];
for (Contact cnt:myList) {
system.debug(cnt.Id + ' ' + cnt.email + ' ' + cnt.firstname + ' ' + cnt.lastname);
}
