Doubt about mysql query

Asked

Viewed 40 times

0

I performed a Query in the Mysql database, but I realized that I brought duplicate values. I didn’t understand where the problem might be in Query, follow the query.

select  usuarios.nome as usuario,forma_pagamento.nome as forma, status.nome as statu, viagens.valor 
as viagemValor,  viagens.distancia as km, viagens.valor_extra as extra, viagens.data_criado as dtcriado,
entregadores.nome as entregadorNome
from viagens 
inner join entregadores
on (viagens.entregadores_id= entregadores.entregadores_id)
inner join usuarios
on (viagens.usuarios_id= usuarios.usuarios_id)
inner join forma_pagamento
on (viagens.forma_pagamento_id=forma_pagamento.forma_pagamento_id)
inner join status
on (viagens.status_id=status.status_id)
where empresas_id= 11 and viagens.deleted = 0
and viagens.data_criado > '2018-03'
mysql
  • 1

    To answer, we would need to know how the data model is defined. Most likely it is not duplicated tuuuuudo, there must be information that is generating the Cartesian product. Most likely this entregadores. It is possible to have more than one courier for a trip?

1 answer

0

Actually there is no problem in the query, for the data not to be duplicated it is necessary to add the distinct in the select. Among the several reasons for this to occur (Duplicated data display) the most I see are tables with relationship 1 para muitos and muitos para muitos, follows how your query should look:

select distinct
    usuarios.nome as usuario,
    forma_pagamento.nome as forma, 
    status.nome as statu, 
    viagens.valor as viagemValor,  
    viagens.distancia as km, 
    viagens.valor_extra as extra, 
    viagens.data_criado as dtcriado,
    entregadores.nome as entregadorNome
from viagens  
inner join entregadores on (viagens.entregadores_id= entregadores.entregadores_id)
inner join usuarios on (viagens.usuarios_id= usuarios.usuarios_id)
inner join forma_pagamento on (viagens.forma_pagamento_id=forma_pagamento.forma_pagamento_id)
inner join status on (viagens.status_id=status.status_id)
where empresas_id= 11 
    and viagens.deleted = 0
    and viagens.data_criado > '2018-03'

Browser other questions tagged mysql

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