Recover orders customer 1. DUPLICATION ERROR

Asked

Viewed 53 times

1

inserir a descrição da imagem aquiMy code: 

 SELECT   Pedido.NumPedido
    , Pedido.CdCliente
    , Cliente.Nome
    , Produto.CdProduto
    , Produto.Descricao
    , PedidoItem.Quantidade
    , PedidoItem.ValorUnitario
    , PedidoItem.ValorTotalItem

    FROM Cliente

     INNER JOIN   Pedido ON Cliente.CdCliente = Pedido.CdCliente
     INNER JOIN   PedidoItem ON Cliente.CdCliente = PedidoItem.CdCliente
     INNER JOIN   Produto ON PedidoItem.CdProduto = Produto.CdProduto

    WHERE  (PedidoItem.ValorUnitario * PedidoItem.Quantidade = PedidoItem.ValorTotalItem)  
    AND    (Cliente.Nome = 'ELETROMATIC DE GARCA LTDA')
    AND (Pedido.NumPedido = '1')

    ORDER BY ValorTotalItem ASC

However, if I shoot AND (Pedido.NumPedido = '1') the results double.

Ex: Codpedido 100, 101, 110 = Numpedido 1 and Codpedido 106 = Numpedido

Except the code stays:

Codpedido 100, 101, 110, 106 and Numpedido 8 and Numpedido 1.

sql sql-server sql-server-2014

  • have how to explain/format this example better? got confused

  • Hello, rLinhares. I put an image for better visualization. I did a "gambiarra" to be able to show without duplicity, but, it is wrong, because the product Test 106 has as Numpedido the value 8.

  • Hello, @Diego. I was able to identify my error. I was double-referencing Cdproduct without need. My mistake was in this Inner Join ( INNER JOIN PedidoItem ON Cliente.CdCliente = PedidoItem.CdCliente ), where in fact, I should relate to Request.Numpedido, so that there would be no duplications. Thank you for your attention.

1 answer

1

According to the table and query you showed, apparently the result is not duplicated because: In the clause WHERE I noticed that to get customer’s orders ELECTROMATIC DE GARCA LTDA you restrict the results to values Value totalitem = Value unitario * Quantity, this way there may be different orders with equal products that is your case. From what I understand you restrict to On request = '1' so the information does not duplicate, but rather by displaying various orders that contain the same product. If you don’t want to make the distinction on request try using the GROUP BY.

  • Hello, Peterson. I was able to identify my error. I was double-referencing Cdproduct without need. My mistake was in this Inner Join ( INNER JOIN PedidoItem ON Cliente.CdCliente = PedidoItem.CdCliente ), where in fact, I should relate to Request.Numpedido, so that there would be no duplications. Thank you for your attention.

Browser other questions tagged sql sql-server sql-server-2014

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