MySQL query duplicates from 2 columns
use aliases for your table especially when you self join

SELECT a1.id, a1.display_name, a1.agents_name, a1.estates_name, t.agents_name, t.estates_name
FROM author_table a1
INNER JOIN(
SELECT agents_name.lestates_name
FROM author_table
GROUP BY agents_name and estates_name
HAVING COUNT(agents_name) &gt;1 AND COUNT(estates_name)&gt;1
)temp t ON a1.agents_name = t.agents_name AND a1.estates_name = t.estates_name;