CREATE TABLE `emlContacts` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `email` varchar(150) default NULL,
  `firstname` varchar(45) default NULL,
  `lastname` varchar(45) default NULL,
  `unsubscribed` tinyint(1) default '0',
  `client_id` int(10) unsigned default NULL,
  `list_id` int(10) unsigned default NULL,
  `delivery_failure` int(10) unsigned default '0',
  PRIMARY KEY  (`id`)
)

SELECT id, email
FROM emlContacts c
WHERE c.id IN(
	SELECT c2.id
	FROM emlContacts c2
	WHERE Exists (
		SELECT email, Count(id)
		FROM emlContacts
		WHERE emlContacts.email = c2.email
		AND emlContacts.list_id = c2.list_id
		AND emlContacts.list_id = 23
		GROUP BY emlContacts.email
		HAVING Count(emlContacts.id) > 1
	)
)
AND c.id NOT IN(
	SELECT Min(id)
	FROM emlContacts AS c3
	WHERE Exists (
		SELECT email, Count(id)
		FROM emlContacts
		WHERE emlContacts.email = c3.email
		AND emlContacts.list_id = c3.list_id
		AND emlContacts.list_id = 23
		GROUP BY emlContacts.email
		HAVING Count(emlContacts.id) > 1
	)
	GROUP BY email
)

SELECT c.id
     , c.email
     , c.firstname
     , c.lastname
     , c.unsubscribed
     , c.client_id
     , c.list_id
     , c.delivery_failure
  FROM emlContacts AS c
INNER
  JOIN (
       SELECT list_id
            , email
         FROM emlContacts
       GROUP 
           BY list_id
            , email
       HAVING COUNT(*) > 1
       ) AS dupes
    ON dupes.list_id = c.list_id
   AND dupes.email   = c.email