How to make this condition correctly in Mysql?

Asked

Viewed 39 times

1

I’m wondering which affiliates have repeated enrollment within a company until the exact moment I get to make this query:

SELECT DISTINCT filiados.`Filiado_Id`,
filiados.`Filiado_MatriculaEmpresa` 
FROM filiados

WHERE filiados.`Filiado_Visivel` = 1
AND filiados.`Filiado_MatriculaEmpresa`  = filiados.`Filiado_MatriculaEmpresa` 

GROUP BY filiados.`Filiado_MatriculaEmpresa` 
HAVING COUNT(filiados.`Filiado_MatriculaEmpresa`) > 1

Only this missing condition that is not working properly:

AND filiados.`Filiado_MatriculaEmpresa`  = filiados.`Filiado_MatriculaEmpresa`
mysql sql database
  • 4

    But this condition of yours is beyond logic! filiados.\Affiliate_matriculaempresa` = filiados. `Filiado_matriculaempresa`é o mesmo que1=1outrue=true`. You are comparing the value to itself... It will always return true. So much so that you end up not needing it...

1 answer

3


First you don’t need the condition

filiados.`Filiado_MatriculaEmpresa`  = filiados.`Filiado_MatriculaEmpresa`

because his

GROUP BY filiados.`Filiado_MatriculaEmpresa`

Already groups all records with field Filiado_MatriculaEmpresa equal.

Second you must remove the clause DISTINCT because it will remove the same records. You should also remove the field Filiado_Id of SELECT because otherwise it will be implicitly included in GROUP BY.

If you want to check the repeated ones in another way, keeping the fields you can use a INNER JOIN in the same table:

SELECT f.filiado_id,
       f2.filiado_id AS repetido,
       f.filiado_matriculaempresa
  FROM filiados f
 INNER JOIN filiados f2 ON f2.filiado_matriculaempresa = f.filiado_matriculaempresa
                       AND f2.filiado_id <> f.filiado_id
 WHERE f.filiado_visivel = 1
   AND f.filiado_matriculaempresa  = f.filiado_matriculaempresa

You didn’t mention if you want to delete the records, but there are a few more explanations regarding duplicates in the answer to the question How to delete all duplicates except one? which may also be applied to a SELECT.

Browser other questions tagged mysql sql database

You are not signed in. Login or sign up in order to post.