How to select records with the same value in a column?

Question

MySQL

The table has the following fields:
id, sum, member_id, date, operator_id Can

you tell me how to select all records whose date and operator_id fields are the same, have the same values?

Answer 1

For example like this. I posted it right here, I hope there are no mistakes :)

SELECT `test`.* 
FROM `test` 
LEFT JOIN 
  (SELECT `date`, `operator_id`
  FROM `test`
  GROUP BY `date`, `operator_id`
  HAVING COUNT(*)>1) as `tbl`
ON
  `test`.`date`=`tbl`.`date` AND
  `test`.`operator_id` = `tbl`.`operator_id`
WHERE `tbl`.`date` IS NOT NULL

Answer 2

SELECT *
FROM `test`
WHERE date = operator_id

Answer 3

here is my version

select t1.*
from test t1
where exists (select *
              from test t2
              where t2.id <> t1.id
              and t2.date = t1.date
              and t2.operator_id = t1.operator_id)

this is in case if the fields date and operator_id are necessarily filled. if they can be NULL, then you can wrap them with the COALESCE function