mysql – Duplicity search in table

Question:

I have a table with 3 million phones, and I have the code for each customer. My goal is to find which phone repeats on different clients.

For that, I can count how many times he repeats, but I can't know who he repeats to.

An example of the code follows.

SELECT      PARCEIRO
        ,   TELEFONE
        ,   COUNT(TELEFONE) AS QTD
FROM        tabela
GROUP BY    TELEFONE
        ,   PARCEIRO
HAVING      COUNT(*)>1
ORDER BY    COUNT(PARCEIRO)

Answer:

You can join using the same table with different aliases, as follows:

SELECT t1.PARCEIRO, t1.TELEFONE FROM tabela as t1
JOIN tabela as t2
    /* mesmo telefone */
    ON t1.TELEFONE = T2.TELEFONE 
    /* parceiro diferente */
    and t1.PARCEIRO <> t2.PARCEIRO /* and t1.PARCEIRO != t2.PARCEIRO */
ORDER BY t1.PARCEIRO, t1.TELEFONE