How I can check if a value occur in my table 2 or more times, but it must be on two diffferent dates.
I don't need to know how much times it occur, I only must know if the passed key occur two or more times.
As the table holds many records, I'm looking for a way that in best case not all reccords must be scanned.
id | key | request_datetime
1 | 111 | 05-05-2017
2 | 222 | 06-05-2017
3 | 111 | 06-05-2017
4 | 111 | 06-05-2017
5 | 222 | 06-05-2017
Your question literally translates to a query which aggregates the keys and counts the number of distinct request dates for each key. In the query below I retain only keys having the same request date appearing 3 or more times, but you can change the threshold to whatever you want.
SELECT `key` FROM yourTable GROUP BY `key` HAVING COUNT(DISTINCT request_datetime) > 2 -- or whatever value you want
Note that I had to place the column name
key into backticks as it is a MySQL reserved keyword. You should avoid naming your tables and columns with keywords unless absolutely necessary.