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)
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