SELECT command in MYSQL is duplicating a result (which is not duplicated in the database)

Asked

Viewed 94 times

1

I’m doing a simple select to return a table in Excel, but when I run the command, some rows are being duplicated in the result. Follow the code below:

SELECT
    distinct movimentacao.DATA, movimentacao.COD, CADASTRO_MP.DESCRICAO, CADASTRO_MP.GRUPO, CADASTRO_MP.SUBGRUPO, movimentacao.QUANTIDADE, cadastro_mp.UNIDADE, movimentacao.ENTRADA_SAIDA, movimentacao.ALMOXARIFADO, CADASTRO_MP.ULTIMO_PRECO, CADASTRO_MP.ULTIMO_PRECO * movimentacao.QUANTIDADE  
FROM movimentacao
LEFT JOIN CADASTRO_MP 
    ON CADASTRO_MP.COD = MOVIMENTACAO.COD 
ORDER BY movimentacao.DATA;

Can anyone tell me what I’m doing wrong?

I already looked at some questions here at stackoverflow but could not solve my problem.

mysql
  • 1

    Uses a GROUP BY that solves.

  • Try adding an example of duplicated data. For this to happen, the problem is in the relationship/data of the tables.

  • How many duplicate COD records do you have in the CADASTRO_MP table?

  • As you are using the DISTINCT clause there should be duplicate records in the result. You may get the impression that the lines are duplicated but are not. For example. a text field with extra space makes the field different.

  • DISTINCT does not work when you select more than one column.

1 answer

0


Try it this way:

SELECT distinct movimentacao.ID, movimentacao.DATA, movimentacao.COD, 
CADASTRO_MP.DESCRICAO, CADASTRO_MP.GRUPO,
CADASTRO_MP.SUBGRUPO, movimentacao.QUANTIDADE, 
cadastro_mp.UNIDADE, movimentacao.ENTRADA_SAIDA, 
movimentacao.ALMOXARIFADO, CADASTRO_MP.ULTIMO_PRECO, 
CADASTRO_MP.ULTIMO_PRECO * movimentacao.QUANTIDADE  
FROM movimentacao left JOIN CADASTRO_MP 
ON CADASTRO_MP.COD = MOVIMENTACAO.COD 
GROUP BY movimentacao.ID
ORDER BY movimentacao.DATA

  • This way it doesn’t work because CADASTRO_MP.COD can be repeated, but with a different date... Using GROUP_BY it shows only one record of each . COD

  • I did it! I had to fetch the ID(which is Primary key) from the MOVIMENTACAO_ALMOX table and group it. Thank you!

Browser other questions tagged mysql

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