Doubt Mysql - Query result

Asked

Viewed 45 times

0

Help me with a situation please, I need to get all the records of the table "buyer" and its attributes and if I have in the records of a client in the order table linked to some buyer listed it shows me by buyer:

comprador.nomeCompr, 
comprador.descCatComprador,
COALESCE(sum(mercadoria.precoMercadoria*pedido.quantMercPedido),'0') as 'valorTotal',
COALESCE(sum(pedido.quantMercPedido),'0') as 'volumeTotal'

from comprador
join fornecedor on fornecedor.codComprFornecedor = comprador.codCompr
left join pedido on pedido.codFornPedido = fornecedor.codForn
left join mercadoria on mercadoria.codMercadoria = pedido.codMercPedido
where comprador.codCompr in (select codCompr from comprador)
and pedido.codAssocPedido = '4102'
group by comprador.codCompr order by valorTotal desc

But in this case he returns only the buyers who have ordered and not the ones who are zeroed, I would like him to return also the buyers who are zeroed, If I do not use the condition Where request.codAssocPedido = '4102' it returns all values including zeroes, but brings also clients I do not want to list, below the prints.

inserir a descrição da imagem aqui inserir a descrição da imagem aqui inserir a descrição da imagem aqui inserir a descrição da imagem aqui inserir a descrição da imagem aqui

mysql sql mysqli

  • If you are already working with the buyer table in the most external SELECT why where comprador.codCompr in (select codCompr from comprador)? Won’t this always be true? As you have no restriction for customers for what reason says behind customers you don’t want to list?

  • Actually I created this ambiguity purposely, but for example I am saying in the '''and request.codAssocPedido = '4102'''' line that it should pull only the associated "customer" actually from the order table

1 answer

0

You can try the following change in your SELECT-->

Instead of using this JOIN clause:

`join fornecedor on fornecedor.codComprFornecedor = comprador.codCompr`

Use as a LEFT JOIN to return buyers without a tied supplier:

left join fornecedor on fornecedor.codComprFornecedor = comprador.codCompr

Browser other questions tagged mysql sql mysqli

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