Find duplicates in a database with coalesce

Question

I am trying to retrieve the duplicates in the ACL table (I created some on purpose).

I wrote a query to retrieve them but unfortunately my query "doesn't do the job". I do not get the duplicates, instead of this, I get as a return a full view of the table ACL (like the select * does), but with items sorted. Duplicates are inside, but they are not "isolated" like I want.

With this query I only want to detect the duplicates. Duplicates are for me: exact same information in every column (except the index), like:

1  TOTO   BLABLA   123
2  TOTO   BLABLA   123

and this is however not a duplicate for me:

1   TOTO    BLABLA  123
2   TOTO    WHAOUH  123

My MYSQL version is 5.0.2.

I already googled it but unfortunately I couldn't get it working in the way I want.

SELECT * FROM `acl` t1, 
                        (SELECT `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv`
                          FROM `acl`
                          GROUP BY `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv`
                        ) t2
                        WHERE 
                        coalesce(t1.`vara`, '1') = coalesce(t2.`vara`, '1')
                        AND coalesce(t1.`varb`, '1') = coalesce(t2.`varb`, '1')
                        AND coalesce(t1.`varc`, '1') = coalesce(t2.`varc`, '1')
                        AND coalesce(t1.`vard`, '1') = coalesce(t2.`vard`, '1')
                        AND coalesce(t1.`vare`, '1') = coalesce(t2.`vare`, '1')
                        AND coalesce(t1.`varf`, '1') = coalesce(t2.`varf`, '1')
                        AND coalesce(t1.`varg`, '1') = coalesce(t2.`varg`, '1')
                        AND coalesce(t1.`varh`, '1') = coalesce(t2.`varh`, '1')
                        AND coalesce(t1.`vari`, '1') = coalesce(t2.`vari`, '1')
                        AND coalesce(t1.`varj`, '1') = coalesce(t2.`varj`, '1')
                        AND coalesce(t1.`vark`, '1') = coalesce(t2.`vark`, '1')
                        AND coalesce(t1.`varl`, '1') = coalesce(t2.`varl`, '1')
                        AND coalesce(t1.`varm`, '1') = coalesce(t2.`varm`, '1')
                        AND coalesce(t1.`varn`, '1') = coalesce(t2.`varn`, '1')
                        AND coalesce(t1.`varo`, '1') = coalesce(t2.`varo`, '1')
                        AND coalesce(t1.`varp`, '1') = coalesce(t2.`varp`, '1')
                        AND coalesce(t1.`varq`, '1') = coalesce(t2.`varq`, '1')
                        AND coalesce(t1.`varr`, '1') = coalesce(t2.`varr`, '1')
                        AND coalesce(t1.`vars`, '1') = coalesce(t2.`vars`, '1')
                        AND coalesce(t1.`vart`, '1') = coalesce(t2.`vart`, '1')
                        AND coalesce(t1.`varu`, '1') = coalesce(t2.`varu`, '1')
                        AND coalesce(t1.`varv`, '1') = coalesce(t2.`varv`, '1') 
                        ";  

Many thanks in advance for your reply!

Answer 1

I solved my problem like this

SELECT count(*) count, `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, 
   `varg`, `varh`, `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, 
   `varo`, `varp`, `varq`, `varr`, `vars`, `vart`, `varu`, `varv` FROM `acl` GROUP BY `vara`, `varb`, `varc`, `vard`, `vare`, `varf`, `varg`, `varh`, 
     `vari`, `varj`, `vark`, `varl`, `varm`, `varn`, `varo`, `varp`, 
     `varq`, `varr`, `vars`, `vart`, `varu`, `varv` HAVING COUNT(*) > 1