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

Question

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

Asked 4 years, 10 months ago

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.

1

Uses a GROUP BY that solves.

– Kayo Bruno

2020/01/21 at 14:37
Try adding an example of duplicated data. For this to happen, the problem is in the relationship/data of the tables.

– tvdias

2020/01/21 at 14:52
How many duplicate COD records do you have in the CADASTRO_MP table?

– Reginaldo Rigo

2020/01/21 at 16:05
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.

– anonimo

2020/01/21 at 16:43
DISTINCT does not work when you select more than one column.

– Reginaldo Rigo

2020/01/21 at 18:25

1 answer

Browser other questions tagged mysql

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

by Brendon Iwata • **323** points · Answer 1 · 2020-01-21T14:45:09+00:00

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