Komarzer Komarzer - 1 year ago 65
PHP Question

Get duplicate columns with a SQL query

I have a User table with id_user, firstname and name columns.
I want to list all the duplicate values.

I did this :

$query = $this->db->select('user.nom, COUNT(*) as count', false)
->having('COUNT(*) >', 1);

return $query->get()->result();

This works, but only for the name. It gives the name duplicated, with the count of how many times it appears. The thing is sometimes there are two users with same name but different firstnames. How can I check duplicates by concatening two columns ?

I tried to use GROUP_CONCAT with no success. I also tried to do this, I wanted to concat the name and the firstname, and group_by it, but doesn't work :

$query = $this->db->select('CONCAT(user.nom, ' ', user.prenom) as usr, COUNT(*) as count', false)
->having('COUNT(*) >', 1);

return $query->get()->result();

What can I use to make it work ?

Answer Source

Double GROUP-BY approach:

$query = $this->db->select('user.name, user.prenom, COUNT(*) as count', false)
                        ->group_by('user.name, user.prenom')
                        ->having('COUNT(*) >', 1);

    return $query->get()->result();

CONCAT approach:

$sql = "SELECT CONCAT(user.name, ', ', user.prenom) AS name, COUNT(*) FROM user GROUP BY name HAVING COUNT(*) > 1";

JOIN approach:

$sql = "SELECT * FROM user AS a JOIN user AS b ON a.name = b.name AND a.prenom = b.prenom AND a.id != b.id"
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download