How to select all rows which have same value in some column
SELECT * FROM employees e1, employees e2 WHERE e1.phoneNumber = e2.phoneNumber AND e1.id != e2.id;Update : for better performance and faster query its good to add e1 before *
SELECT e1.* FROM employees e1, employees e2 WHERE e1.phoneNumber = e2.phoneNumber AND e1.id != e2.id;
How about
SELECT *FROM EmployeesWHERE PhoneNumber IN ( SELECT PhoneNumber FROM Employees GROUP BY PhoneNumber HAVING COUNT(Employee_ID) > 1 )SQL Fiddle DEMO
You can do this without a JOIN:
SELECT *FROM (SELECT *,COUNT(*) OVER(PARTITION BY phone_number) as Phone_CT FROM YourTable )subWHERE Phone_CT > 1ORDER BY phone_number, employee_idsDemo: SQL Fiddle