Try using a GROUP BY, and then JOIN those results to your original table.
Assume we have this table:
ID FName SName Email
1 Joe Smith Joe@xxx.com
2 Mike Jones Mike@xxx.com
3 Joe Smith Joe@xxx.com
4 Mike Smith Mike.S@xxx.com
If we use GROUP BY, We can add a count of the rows:
SELECT [FName]
,[SName]
,[Email]
,COUNT(*) AS Cnt
FROM [Testing].[dbo].[MyTable]
GROUP BY [FName]
,[SName]
,[Email]
FName SName Email Cnt
Joe Smith Joe@xxx.com 2
Mike Jones Mike@xxx.com 1
Mike Smith Mike.S@xxx.com 1
SO it's simple to convert that to just give duplicates:
SELECT [FName]
,[SName]
,[Email]
FROM [Testing].[dbo].[MyTable]
GROUP BY [FName]
,[SName]
,[Email]
HAVING COUNT(*) > 1
:
FName SName Email
Joe Smith Joe@xxx.com
Now all you have to do is JOIN them back together to get the IDs:
SELECT a.* FROM Testing.dbo.MyTable a
JOIN (SELECT [FName]
,[SName]
,[Email]
FROM [Testing].[dbo].[MyTable]
GROUP BY [FName]
,[SName]
,[Email]
HAVING COUNT(*) > 1) b
ON a.Email = b.Email
AND a.FName = b.FName
AND a.SName = b.SName
ID FName SName Email
1 Joe Smith Joe@xxx.com
3 Joe Smith Joe@xxx.com