How to find duplicate columns that have the same values ​​in more than one column (column names are given)


I am using mysql db and created a table as.

 student_id roll_no student_name    date_of_join    email_id
    1   ERD001  Ankit   2013-10-16  [email protected]
    2   ERD002  Aman    2013-10-16  [email protected]
    3   ERD003  Alok    2013-10-16  [email protected]
    4   ERD004  Anshuman    2013-10-16  [email protected]
    6   ERD002  Anil    2013-10-16  [email protected]
    7   ERD004  Hemant  2013-10-17  [email protected]

Here I want to select all records which are having same roll number and date of join. i.e. i want to fetch records-

  2             ERD002      Aman            2013-10-16  [email protected]
    6           ERD002      Anil                2013-10-16  [email protected]

because both records having same (ERD002,2013-10-16 ).

I have tried as.

SELECT * FROM students
GROUP BY roll_no,date_of_join
HAVING COUNT(student_id)>1

But It gives single record.

Here is a query that should work:

SELECT * FROM students s
(SELECT roll_no, date_of_join FROM students
GROUP BY roll_no, date_of_join
HAVING COUNT(student_id)>1) rd on s.roll_no = rd.roll_no and s.date_of_join = rd.date_of_join
ORDER BY s.roll_no, s.date_of_join