MySql comapre rows in same table if one row status value is 5-CodePudding

Table stucture

name	surname	phone	email	status
Jonh	Red	123	johnmail	5
Mary	Blue	333	marymail	0
Helen	Green	234	marymail	0
Jonh	Red	123	johnmail	0

How can I compare these rows with name, surname, email and phone, and select (display) only Jonh Red because that row exist two time and one row have status 5?

Thanks in advance.

CodePudding user response：

You could try an aggregation approach here:

SELECT name, surname, phone, email
FROM yourTable
GROUP BY name, surname, phone, email
HAVING COUNT(*) > 1 AND SUM(status = 5) > 0;

This would return every tuple (sans status values) which both appear in duplicate and also have a status value of 5 in at least one of the duplicates.